Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Insert multiple rows using static values

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
jerryte

Active User


Joined: 29 Oct 2010
Posts: 183
Location: Toronto, ON, Canada

PostPosted: Fri Aug 17, 2012 11:24 pm    Post subject: Insert multiple rows using static values
Reply with quote

I am stuck on the db2 syntax for a statement. I am usually helping coworkers with SQL but this time I need help. I tried the SQL reference manual but no luck.

I want to do a multiple row insert using static values. I tried the below sql but db2 gives me a cryptic error message
Code:
INSERT INTO TABLE1
  (COL1, COL2)
VALUES
   (1, 'SAMPLE'),
   (2, 'SAMPLE')
FOR 2 ROWS
;

COL1 is Decimal and COL2 is char. Can someone provide me with the proper syntax? Thanks.
Back to top
View user's profile Send private message

Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1738
Location: Bloomington, IL

PostPosted: Fri Aug 17, 2012 11:45 pm    Post subject:
Reply with quote

And the cryptic error message would be...?
Back to top
View user's profile Send private message
jerryte

Active User


Joined: 29 Oct 2010
Posts: 183
Location: Toronto, ON, Canada

PostPosted: Fri Aug 17, 2012 11:51 pm    Post subject:
Reply with quote

Code:
 42601(-104)[IBM][CLI Driver][DB2] SQL0104N  An unexpected token "," was found following "". 
Expected tokens may include:  "FOR <END-OF-STATEMENT> NOT ATOMIC".  SQLSTATE=42601
 (0.05 secs)
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Sat Aug 18, 2012 1:33 am    Post subject:
Reply with quote

Hello,

Suggest you try 2 insert statements with one VALUES each.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Sat Aug 18, 2012 3:36 am    Post subject:
Reply with quote

a suggestion from Sushanth Bobby (from 3 years ago, to a 5 year old thread)
about Syntax to insert multiple rows from SPUFI
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Sat Aug 18, 2012 5:08 am    Post subject:
Reply with quote

I think that you will find that on the mainframe,
the FOR n ROWS clause is for
multiple-row insert/fetch from/into Host Variable Arrays.
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Sat Aug 18, 2012 10:11 am    Post subject:
Reply with quote

If you are doing this in SPUFI, QMF the post Dick has pointed to should do the trick.

If you're doing it using COBOL program, Multiple-row INSERT and FETCH statements can be of your interest, as Dick has hinted to.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Sat Aug 18, 2012 11:47 pm    Post subject:
Reply with quote

Anuj, good links.
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Mon Aug 20, 2012 4:45 pm    Post subject:
Reply with quote

I found that link rather comprehensive compared to what they said in DB2 9 and DB2 10. And without knowing the DB2 version in question, it also indicated that DB2 8 onwards you can use the stated concept.
Back to top
View user's profile Send private message
jerryte

Active User


Joined: 29 Oct 2010
Posts: 183
Location: Toronto, ON, Canada

PostPosted: Mon Aug 20, 2012 7:00 pm    Post subject:
Reply with quote

It seems a multi row insert using static values can be done only from a program using a host array of values. A multi row insert using static values cannot be done using Spufi or any other dynamic sql tool. To do this I have to code a full select statement after the INSERT statement. The SQL reference manual is not clear about this. It teases that it can be done but when I try it it then teases again with cryptic error messages. Luckily this forum is available for expert help. Thanks for the responses.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Mon Aug 20, 2012 8:21 pm    Post subject:
Reply with quote

"using static values can be done only from a program using a host array of values" = contradictio in terminis
or static values or host array, it can't be both.

Not that it helps but just to be clear :
multi row insert using static values can be done :
Code:
insert into table1 values (1,'A') for 2 rows
will (try to) insert 2 rows, but with the same values.
What can NOT be done (in DB2 z/OS) is specifying an array using static values/literals.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Overlaying one set of charater values... Kevin Lindsley SYNCSORT 7 Sat Nov 05, 2016 3:21 am
No new posts Row-Numbers of distinct rows? Auryn DB2 1 Thu Oct 20, 2016 4:38 pm
No new posts Low values Results from VARCHAR FORMAT balaji81_k DB2 10 Thu Oct 20, 2016 1:18 am
No new posts How can we create a flat file in JAVA... rakesh.v18 Java & MQSeries 7 Fri Sep 23, 2016 10:46 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us