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

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 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  lt END-OF-STATEMENT gt  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

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

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

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

[sql] insert multiple rows into DB2 database

Examples related to sql

Examples related to db2