1. Forum
  2. Usenet
  3. COMP.DATABASES.IBM-DB2

  • Generate INSERT statements out of data in DB2-tables

    From ryaneberly@gmail.com@21:1/5 to ileana...@raiffeisen.ro on Tue Dec 17 05:02:52 2019
    The following will generate an insert statement for you in DB2 (i).

    create or replace function insertme(
    schemaname varchar(50),
    tablename varchar(50),
    rrnno integer
    )
    returns varchar(20000)
    begin
    declare mysql varchar(20000);
    declare mysql2 varchar(20000);
    DECLARE C1 CURSOR
    FOR DYNSQL;
    select
    'select ''insert into '||tablename||' values(''||'
    || listagg(''''''''' concat rtrim(replace(' ||column_name || ','''''''',''''''''''''))||''''''''',' ||'','' || ')
    || '||'')'''
    || ' from '||schemaname||'.'||tablename||' a where rrn(a)=' || rrnno
    into mysql
    from syscolumns where table_name=tablename and table_schema=schemaname;
    PREPARE DYNSQL from mysql;
    OPEN C1;
    fetch from c1 into mysql2;
    close c1;
    return mysql2;
    end
    ;

    Example use:
    select insertme(schema_name,table_name,rrn(a))
    from schema_name.table_name a limit 1

    On Tuesday, November 8, 2016 at 4:22:12 AM UTC-5, ileana...@raiffeisen.ro wrote:
    Hi,

    DBeaver can do that even for the great DB2. Right click on the table and "Generate SQL". It will export all insert statements for each row.


    On Wednesday, October 14, 1998 at 10:00:00 AM UTC+3, Styrk Finne wrote:
    IS it possible to generate INSERT statements out of data in tables ??

    If you have table employee with the following fields and data :

    empno name address ------------------------------------------------
    0001 Erik Bergen
    0002 Lise Oslo

    and what I want is the result :

    Insert into employee values ('0001','Erik','Bergen');
    Insert into employee values ('0002','Lise','Oslo');

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From AngocA@21:1/5 to AngocA on Wed May 19 08:41:33 2021
    On Wednesday, May 19, 2021 at 11:36:37 AM UTC-4, AngocA wrote:
    This is an adapted version for Db2 LUW:

    db2 -td@

    CREATE OR REPLACE FUNCTION generate_insert(
    schemaname VARCHAR(50),
    tablename VARCHAR(50),
    rownu INTEGER
    )
    RETURNS VARCHAR(2000)
    BEGIN
    DECLARE stmt1 VARCHAR(2000);
    DECLARE stmt2 VARCHAR(2000);
    DECLARE mycursor CURSOR
    FOR dynsql;

    SELECT
    'SELECT ''INSERT INTO ' || tablename || ' VALUES (''||'
    || listagg(''''''''' CONCAT RTRIM(REPLACE(' || COLNAME || ','''''''',''''''''''''))||''''''''',' ||'','' || ')
    || '||'')'''
    || ' FROM ' || schemaname || '.' || tablename
    || ' WHERE RID() = ' || rownu
    INTO stmt1
    FROM SYSCAT.COLUMNS
    WHERE TABSCHEMA = schemaname
    AND TABNAME = tablename;

    PREPARE dynsql FROM stmt1;
    OPEN mycursor;
    FETCH FROM mycursor INTO stmt2;
    CLOSE mycursor;
    RETURN stmt2;
    END
    @

    Thanks to Ryane for the initial idea.
    I will post this code in my GitHub's gists.

    One way to call it could be:

    SELECT generate_insert('DB2INST1', 'EMPLOYEE', RID()), DB2INST1.EMPLOYEE.*
    FROM DB2INST1.EMPLOYEE
    FETCH FIRST 1 ROW ONLY
    @

    However, the query has to be improved, in order to show the column names before the values, or generate the insert with the same column order. Also, Null values, or Blob values will be wrongly generated.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From AngocA@21:1/5 to All on Wed May 19 08:36:34 2021
    This is an adapted version for Db2 LUW:

    db2 -td@

    CREATE OR REPLACE FUNCTION generate_insert(
    schemaname VARCHAR(50),
    tablename VARCHAR(50),
    rownu INTEGER
    )
    RETURNS VARCHAR(2000)
    BEGIN
    DECLARE stmt1 VARCHAR(2000);
    DECLARE stmt2 VARCHAR(2000);
    DECLARE mycursor CURSOR
    FOR dynsql;

    SELECT
    'SELECT ''INSERT INTO ' || tablename || ' VALUES (''||'
    || listagg(''''''''' CONCAT RTRIM(REPLACE(' || COLNAME || ','''''''',''''''''''''))||''''''''',' ||'','' || ')
    || '||'')'''
    || ' FROM ' || schemaname || '.' || tablename
    || ' WHERE RID() = ' || rownu
    INTO stmt1
    FROM SYSCAT.COLUMNS
    WHERE TABSCHEMA = schemaname
    AND TABNAME = tablename;

    PREPARE dynsql FROM stmt1;
    OPEN mycursor;
    FETCH FROM mycursor INTO stmt2;
    CLOSE mycursor;
    RETURN stmt2;
    END
    @

    Thanks to Ryane for the initial idea.
    I will post this code in my GitHub's gists.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From AngocA@21:1/5 to All on Thu May 20 21:20:30 2021
    I published the code in GitHub: https://gist.github.com/angoca/f41f8b7e71be8b701c3081bc06af2cab
    And I wrote an article about this in my blog: https://angocadb2.blogspot.com/2021/05/generate-insert-statement-from-current.html

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)