|
View previous topic :: View next topic
|
| Author |
Message |
jerryte
Active User
Joined: 29 Oct 2010
Posts: 202
Location: Toronto, ON, Canada
|
|
|
|
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 |
|
 |
Akatsukami
Global Moderator
Joined: 03 Oct 2009
Posts: 1788
Location: Bloomington, IL
|
|
|
|
| And the cryptic error message would be...? |
|
| Back to top |
|
|
jerryte
Active User
Joined: 29 Oct 2010
Posts: 202
Location: Toronto, ON, Canada
|
|
|
|
| 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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
Suggest you try 2 insert statements with one VALUES each. |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
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 |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| Anuj, good links. |
|
| Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India
|
|
|
|
| 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 |
|
|
jerryte
Active User
Joined: 29 Oct 2010
Posts: 202
Location: Toronto, ON, Canada
|
|
|
|
| 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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
"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 |
|
|
|
|
