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.
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')
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...)
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
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.
Source: Stackoverflow.com