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.

Answer 1

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.

Answer 2

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