Build an INSERT statement

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