36

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.

Frantumn
  • 1,725
  • 7
  • 36
  • 61

5 Answers5

36

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.

bhamby
  • 15,112
  • 1
  • 45
  • 66
  • 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? – bhamby Jul 31 '12 at 17:48
  • `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 – Frantumn Jul 31 '12 at 18:01
  • It appears that you cannot directly select a `NULL` from [`SYSIBM.SYSDUMMY1`](http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db2z10.doc.sqlref/src/tpc/db2z_sysibmsysdummy1table.htm), 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. – bhamby Jul 31 '12 at 18:29
  • 1
    So in place of `NULL` put `SELECT NULLIF(0,0)` ? – Frantumn Jul 31 '12 at 18:47
  • - *for whatever reason, I never really understood why* - **Because dinosaurs don't understand your needs.** – ramazan polat Jan 22 '21 at 13:44
  • Can confirm this works. – tmaurst May 10 '22 at 19:31
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')
Hogan
  • 69,564
  • 10
  • 76
  • 117
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
Esperento57
  • 16,521
  • 3
  • 39
  • 45
3

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...)

grimpressive
  • 51
  • 3
0

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.

Keyhan
  • 2,370
  • 2
  • 19
  • 22