IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Inserting multiple rows using single INSERT statement?


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
J.P.R.Kumar

New User


Joined: 02 Apr 2008
Posts: 11
Location: Chennai

PostPosted: Thu Jun 19, 2008 12:17 pm
Reply with quote

Hi all,

can we insert multiple rows using single insert statement, the code is as follows, but this is not working, can some one help .

insert into employ
(001,'phani','l&t infotech chennai'),
(002,'mani','l&t infotech chennai'),
(003,'ani','l&t infotech chennai');
Back to top
View user's profile Send private message
ascelepius

New User


Joined: 16 Jun 2008
Posts: 35
Location: bangalore

PostPosted: Thu Jun 19, 2008 12:23 pm
Reply with quote

No you cannot, DB2 manuals say,

Quote:

There are three forms of this statement:
- The INSERT via VALUES form is used to insert a single row into the table or view using the values provided or referenced.
- The INSERT via SELECT form is used to insert one or more rows into the table or view using values from other tables, or views, or both.
- The INSERT via FOR n ROWS form is used to insert multiple rows into the table or view using values provided from host-variable arrays.


regards,
asci(i)
Back to top
View user's profile Send private message
J.P.R.Kumar

New User


Joined: 02 Apr 2008
Posts: 11
Location: Chennai

PostPosted: Thu Jun 19, 2008 2:35 pm
Reply with quote

hi ascelepius,

The db2 manual only says that there is a provision for the insertion of multiple rows using single insert statement (the code which i pasted is a ex.). but when i tried its not working.
Back to top
View user's profile Send private message
ascelepius

New User


Joined: 16 Jun 2008
Posts: 35
Location: bangalore

PostPosted: Thu Jun 19, 2008 2:55 pm
Reply with quote

Well, if you read the manual carefully you will note that this can be achieved only with host variables/arrays...

regards,
asci(i)
Back to top
View user's profile Send private message
abhishekmdwivedi

New User


Joined: 22 Aug 2006
Posts: 95
Location: india

PostPosted: Thu Jun 19, 2008 3:34 pm
Reply with quote

Hi Kumar,

Here's a link which will help you on this .

http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/admin/r0004231.htm

Kindly check your DB2 version for the multi-row insert query.

Code:

INSERT INTO DEPARTMENT (DEPTNO, DEPTNAME, ADMRDEPT)
VALUES ('B11', 'PURCHASING', 'B01'),
            ('E41', 'DATABASE ADMINISTRATION', 'E01')


Quote:
Well, if you read the manual carefully


And "ascelepius" I think this applies to you.
icon_rolleyes.gif
Back to top
View user's profile Send private message
abhishekmdwivedi

New User


Joined: 22 Aug 2006
Posts: 95
Location: india

PostPosted: Thu Jun 19, 2008 3:35 pm
Reply with quote

Igonre previous link use the below one .

http://publib.boulder.ibm.com/infocenter/db2luw/v8/topic/com.ibm.db2.udb.doc/admin/r0000970.htm
Back to top
View user's profile Send private message
ascelepius

New User


Joined: 16 Jun 2008
Posts: 35
Location: bangalore

PostPosted: Thu Jun 19, 2008 3:51 pm
Reply with quote

@abhishek

Oops! I refer to the DB2 manuals for ZO/S( i didnot google).

You are right. I overlooked the fact that queries related to DB2 UDB on non-mainframe could appear here.

For DB2 V8 on Z O/S, this is not possible

regards,
asci(i)
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts INCLUDE OMIT COND for Multiple values... DFSORT/ICETOOL 5
No new posts To get the count of rows for every 1 ... DB2 3
No new posts Replace Multiple Field values to Othe... DFSORT/ICETOOL 12
No new posts Multiple table unload using INZUTILB DB2 2
No new posts Grouping by multiple headers DFSORT/ICETOOL 7
Search our Forums:

Back to Top