Generate INSERT statements out of data in DB2-tables

[i***@raiffeisen.ro]

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.

IS it possible to generate INSERT statements out of data in tables ??
empno name address
------------------------------------------------
0001 Erik Bergen
0002 Lise Oslo
Insert into employee values ('0001','Erik','Bergen');
Insert into employee values ('0002','Lise','Oslo');

[r***@gmail.com]

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

Post by i***@raiffeisen.ro
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.

IS it possible to generate INSERT statements out of data in tables ??
empno name address
------------------------------------------------
0001 Erik Bergen
0002 Lise Oslo
Insert into employee values ('0001','Erik','Bergen');
Insert into employee values ('0002','Lise','Oslo');

[AngocA]

This post might be inappropriate. Click to display it.

[AngocA]

Post by AngocA
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.

[AngocA]

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