Insert Multiple Rows into Db2 Database

insert multiple rows into DB2 database

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.

How do I insert multilple rows into a DB2 in one statement?

You can use batched statements instead:

String values = "INSERT INTO GROUPS " +
"(GROUP_NAME,ENTRY_NAME,ENTRY_TYPE,LAST_REQ,CREATE_BY,REQ_BY,LAST_CHANGED,LAST_REQ_TIME) " +
"VALUES (?,?,?,?,?,?,?,?)";

// ... 

statement = conn.prepareStatement(values);

// ... 

while(i < addedResources.size()){
   statement.setString(addedResources.get(i)).getGROUP_NAME().trim());
   statement.setString(addedResources.get(i)).getENTRY_NAME().trim());

   // ...  

   statement.addBatch();
}
statement.executeBatch();

Update or Insert Multiple Rows into a Table in DB2

Try this:

--#SET TERMINATOR @
DECLARE GLOBAL TEMPORARY TABLE SESSION.MYTAB (ID INT, NAME VARCHAR(10), DESCRIPTION VARCHAR(20)) 
WITH REPLACE ON COMMIT PRESERVE ROWS NOT LOGGED
@

BEGIN
  DECLARE V_ID1   INT         DEFAULT 1; 
  DECLARE V_NAME1 VARCHAR(10) DEFAULT 'NAME1'; 
  DECLARE V_DESC1 VARCHAR(20) DEFAULT 'DESC1'; 
  DECLARE V_ID2   INT         DEFAULT 2; 
  DECLARE V_NAME2 VARCHAR(10) DEFAULT 'NAME2'; 
  DECLARE V_DESC2 VARCHAR(20) DEFAULT 'DESC2'; 

  PREPARE S1 FROM '
  MERGE INTO SESSION.MYTAB T USING 
  (
  VALUES
    (CAST(? AS INT), CAST(? AS VARCHAR(10)), CAST(? AS VARCHAR(20)))
  , (CAST(? AS INT), CAST(? AS VARCHAR(10)), CAST(? AS VARCHAR(20)))
  ) S (ID, NAME, DESCRIPTION) ON T.ID = S.ID
  WHEN     MATCHED THEN UPDATE SET NAME = S.NAME, DESCRIPTION = S.DESCRIPTION 
  WHEN NOT MATCHED THEN INSERT (ID, NAME, DESCRIPTION) VALUES (S.ID, S.NAME, S.DESCRIPTION)
  ';

  EXECUTE S1 USING 
    V_ID1, V_NAME1, V_DESC1
  , V_ID2, V_NAME2, V_DESC2;
END
@

SELECT * FROM SESSION.MYTAB
@

Db2 Multi Insert using LOOP

Compound statements in Db2 for z/OS are supported in routines and triggers only.

You can't use them as standalone statements.

Your DECLARE & SET statements are erroneous - you don't provide a data type in DECLARE and do it in SET. It should be:

DECLARE varcnt int DEFAULT 0;

As for you case. You may do the same with a single statement:

insert into HEROI.TESTD (PK,XML_TYPE)
WITH T (I) AS 
(
SELECT 0 FROM SYSIBM.SYSDUMMY1
    UNION ALL
SELECT I+1 FROM T WHERE I < 10
)
SELECT I, '<test>Hello World</test>'
FROM T

db2 set incrementing values using insert statement

insert into MYTABLE1 (id, col1, col2)
select my_id, 50 col1, col2 + row_number() over(order by my_id) from
(
    select MY_ID, m.col2
    from MYTABLE2 t2, (select max(col2) as col2 from MYTABLE1) m
) t2
-- if you don't want to insert IDs which already exist in MYTABLE1
where not exists (select 1 from MYTABLE1 t1 where t1.ID = t2.MY_ID);

---

Related Topics