insert multiple rows into DB2 database

Question

I want to insert multiple rows into a DB2 table. I have a query that looks like this

insert into tableName 
(col1, col2, col3, col4, col5) 
values 
(val1, val2, val3, val4, val5),
(val1, val2, val3, val4, val5),
(val1, val2, val3, val4, val5),
(val1, val2, val3, val4, val5);

This query does't work. I don't know if there is a more syntactically correct way to do this in DB2. But it'd be useful to insert my test data.

Not supported by z/OS. ibm.com/docs/en/db2-for-zos/11?topic=statements-insert — tmaurst
– tmaurst, Commented May 10, 2022 at 19:14

bhamby · Accepted Answer · 2012-07-31 16:07:59Z

39

I'm assuming you're using DB2 for z/OS, which unfortunately (for whatever reason, I never really understood why) doesn't support using a values-list where a full-select would be appropriate.

You can use a select like below. It's a little unwieldy, but it works:

INSERT INTO tableName (col1, col2, col3, col4, col5) 
SELECT val1, val2, val3, val4, val5 FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT val1, val2, val3, val4, val5 FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT val1, val2, val3, val4, val5 FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT val1, val2, val3, val4, val5 FROM SYSIBM.SYSDUMMY1

Your statement would work on DB2 for Linux/Unix/Windows (LUW), at least when I tested it on my LUW 9.7.

edited Jul 31, 2012 at 16:07

answered Jul 31, 2012 at 15:27

bhamby

15.6k1 gold badge49 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

bhamby Over a year ago

I don't think you'd need to put in any indicator (except for the NULL keyword, where appropriate). What is the error code you are getting?

Frantumn Over a year ago

Column NULL is not in any table named in the query. And the fields that I insert NULL do allow for it. Also have got SQLCODE=-206 and -104

bhamby Over a year ago

It appears that you cannot directly select a NULL from SYSIBM.SYSDUMMY1, whose only column is defined as NOT NULL. You can get around that two ways... SELECT NULLIF(0,0) or SELECT CAST(NULL AS INTEGER). I guess DB2 expects to know what the data type is. Hope that helps.

Frantumn Over a year ago

So in place of NULL put SELECT NULLIF(0,0) ?

ramazan polat Over a year ago

- for whatever reason, I never really understood why - Because dinosaurs don't understand your needs.

|

Hogan · Accepted Answer · 2015-12-31 18:24:31Z

19

UPDATE - Even less wordy version

INSERT INTO tableName (col1, col2, col3, col4, col5) 
VALUES ('val1', 'val2', 'val3', 'val4', 'val5'),
       ('val1', 'val2', 'val3', 'val4', 'val5'),
       ('val1', 'val2', 'val3', 'val4', 'val5'),
       ('val1', 'val2', 'val3', 'val4', 'val5')

The following also works for DB2 and is slightly less wordy

INSERT INTO tableName (col1, col2, col3, col4, col5) 
VALUES ('val1', 'val2', 'val3', 'val4', 'val5') UNION ALL
VALUES ('val1', 'val2', 'val3', 'val4', 'val5') UNION ALL
VALUES ('val1', 'val2', 'val3', 'val4', 'val5') UNION ALL
VALUES ('val1', 'val2', 'val3', 'val4', 'val5')

edited Dec 31, 2015 at 18:24

answered Feb 12, 2015 at 19:30

Hogan

70.7k10 gold badges83 silver badges119 bronze badges

2 Comments

Roger Perkins Jul 10 at 17:54

Your db2 statement gives me this message: Exception calling "ExecuteNonQuery" with "0" argument(s): "ERROR [42601] [IBM][CLI Driver][DB2] SQL0199N The use of the reserved word "UNION" following "" is not valid. Expected tokens may include: "FOR <END-OF-STATEMENT> NOT ATOMIC". SQLSTATE=42601

Hogan Jul 11 at 19:04

Just use the comma like in the first example. 10 years ago so I'm not sure, there might have been a version that allowed UNION ALL with VALUES, but if you are using values it is clearer to use comma.

grimpressive · Accepted Answer · 2019-09-17 10:32:14Z

5

I disagree on the comment posted by Hogan. Those instructions will work for IBM DB2 Mini, but it's not the case of DB2 Z/OS.

Here is an example:

Exception data: org.apache.ibatis.exceptions.PersistenceException:
The error occurred while setting parameters

SQL: INSERT INTO TABLENAME(ID_, F1_, F2_, F3_, F4_, F5_) VALUES
 (?,          1,          ?,          ?,          ?,          ?),          
 (?,          1,          ?,          ?,          ?,          ?)


Cause: com.ibm.db2.jcc.am.SqlSyntaxErrorException: 
ILLEGAL SYMBOL ",". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: FOR <END-OF-STATEMENT> NOT ATOMIC. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.25.17

So I can confirm that inline comma separated bulk inserts are not working on DB2 Z/OS (maybe you could feed it some props to get it working...)

answered Sep 17, 2019 at 10:32

grimpressive

711 silver badge3 bronze badges

1 Comment

Hogan Jul 11 at 19:06

This is 6 years later I see this, but just use the SELECT syntax with UNION ALL won't change the speed, it is just a parsing difference.

Esperento57 · Accepted Answer · 2016-11-15 18:36:55Z

4

other method

INSERT INTO tableName (col1, col2, col3, col4, col5)
select * from table(                        
                    values                                      
                    (val1, val2, val3, val4, val5),   
                    (val1, val2, val3, val4, val5),   
                    (val1, val2, val3, val4, val5),   
                    (val1, val2, val3, val4, val5)    
                    ) tmp

answered Nov 15, 2016 at 18:36

Esperento57

17.6k6 gold badges42 silver badges47 bronze badges

Comments

Keyhan · Accepted Answer · 2020-11-25 11:00:15Z

1

None of the above worked for me, the only one working was

insert into tableName  
select 11, 'BALOO' from sysibm.sysdummy1 union all
select 22, nullif('','') AS nullColumn from sysibm.sysdummy1

The nullif is used since it is not possible to pass null in the select statement otherwise.

answered Nov 25, 2020 at 11:00

Keyhan

2,5392 gold badges21 silver badges22 bronze badges

Collectives™ on Stack Overflow

insert multiple rows into DB2 database

5 Answers 5

7 Comments

2 Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

7 Comments

2 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related