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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql] insert multiple rows into DB2 database

Examples related to sql

Examples related to db2