Use this DB2 Stored Procedure to build an INSERT statement. This is very useful when you are developing some Java or .Net program or any other program and need an easy way to do this.
Thanks to Serge Rielau of DB2 Toronto lab for sharing this with the DB2 community. You can also download the script from here.
CREATE TABLE RESULT(text CLOB(2M)); DROP PROCEDURE insertscript; --#SET TERMINATOR @ CREATE PROCEDURE insertscript(IN ptabschema VARCHAR(128), IN ptabname VARCHAR(128), IN pbatchsize INTEGER, IN pterminator VARCHAR(2)) BEGIN DECLARE SQLCODE INTEGER; DECLARE SQLSTATE CHAR(5); DECLARE inserttext CLOB(2M); DECLARE selecttext CLOB(2M); DECLARE insertheader CLOB(2M); DECLARE colname VARCHAR(128); DECLARE colcount INTEGER; DECLARE colno INTEGER; DECLARE rowcount INTEGER; DECLARE insertcount INTEGER; DECLARE colvalue CLOB(128); DECLARE typeid SMALLINT; DECLARE done SMALLINT; DECLARE selectstmt STATEMENT; DECLARE selectcur CURSOR WITH HOLD FOR selectstmt; IF NOT EXISTS(SELECT 1 FROM SYSCAT.TABLES WHERE tabschema = ptabschema AND tabname = ptabname) THEN SIGNAL SQLSTATE '78000' SET MESSAGE_TEXT = 'Table not found (remember case!)'; END IF; DELETE FROM result; SET insertheader = 'INSERT INTO "' || ptabschema || '"."' || ptabname || '" VALUES '; SET selecttext = 'SELECT colno, colvalue, typeid FROM "' || ptabschema || '"."' || ptabname || '" AS T, LATERAL(VALUES '; FOR column AS SELECT colno, colname, typename, codepage FROM SYSCAT.COLUMNS WHERE tabschema = ptabschema AND tabname = ptabname ORDER BY colno DO SET typeid = CASE WHEN typename IN ('SMALLINT', 'INTEGER', 'BIGINT', 'FLOAT', 'DOUBLE', 'REAL', 'DECFLOAT', 'DECIMAL') THEN 1 WHEN typename IN ('CHAR', 'VARCHAR', 'GRAPHIC', 'VARGRAPHIC', 'CHARACTER', 'CLOB', 'DBCLOB') THEN CASE WHEN codepage = 0 THEN 3 ELSE 2 END WHEN typename = 'BLOB' THEN 4 ELSE RAISE_ERROR('78000', 'Unsupported type') END; SET selecttext = selecttext || CASE WHEN colno > 0 THEN ',' ELSE '' END || '(SMALLINT(' || CHAR(colno) || '), ' || CASE WHEN typeid = 1 THEN 'TRIM(CHAR(T."' || colname || '")), ' WHEN typeid = 3 THEN 'HEX(T."' || colname || '"), ' WHEN typeid = 4 THEN 'HEX(VARCHAR(T."' || colname || '")), ' ELSE 'VARCHAR(T."' || colname || '"), ' END || CHAR(typeid) || ')'; SET colcount = colno; END FOR; SET selecttext = selecttext || ') AS S(colno, colvalue, typeid)'; PREPARE selectstmt FROM selecttext; SET rowcount = 0; SET insertcount = 0; SET done = 0; OPEN selectcur; mainloop: WHILE rowcount < pbatchsize DO IF rowcount = 0 THEN SET inserttext = insertheader; END IF; subloop: LOOP FETCH selectcur INTO colno, colvalue, typeid; IF SQLCODE = 100 THEN SET done = 1; LEAVE subloop; END IF; SET inserttext = inserttext || CASE WHEN colno = 0 AND rowcount = 0 THEN '(' WHEN colno = 0 THEN '), ' || CHR(10) || chr(13) || '(' ELSE ',' END || CASE WHEN colvalue IS NULL THEN 'NULL' WHEN typeid = 1 THEN colvalue WHEN typeid = 2 THEN '''' || colvalue || '''' WHEN typeid = 4 THEN 'BLOB(x''' || colvalue || ''')' ELSE 'x''' || colvalue || '''' END; IF colno = colcount THEN SET rowcount = rowcount + 1; END IF; IF rowcount >= pbatchsize THEN LEAVE subloop; END IF; END LOOP; SET inserttext = inserttext || ')' || pterminator; IF rowcount > 0 THEN INSERT INTO RESULT VALUES inserttext; SET rowcount = 0; SET insertcount = insertcount + 1; IF MOD(insertcount, 100) = 0 THEN COMMIT; END IF; ELSE LEAVE mainloop; END IF; END WHILE; END @ --#SET TERMINATOR ; CALL insertscript('myschema', 'mytab', 10, ';');