Generate INSERT statements out of data in DB2-tables

[Styrk Finne]

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

[Frank]

There is no such tool in db2. However, you can export the data to a file,
write a script to process the file or write a C program if you perfer.
Styrk Finne wrote in message <702scl$gjp$1...@readme.online.no>...

[Frank]

[Frank]

Just forget there is a easy way. Use the following SQL
select 'Insert into employee values(',empno,',',name,',',address,')' from
employee;

You will get what you want.

Frank wrote in message <70313m$f...@news.acns.nwu.edu>...

[Duane Lee - ATCX]

Yes - you can build sql commands for later execution using sql. To go
with your sample below you could do the following:

SELECT 'INSERT INTO EMPLOYEE VALUES (',
EMPNO, ', '''
||NAME||''', '''
||ADDRESS||''' );'
FROM EMPLOYEE;

The results of executing this sql would be:

INSERT INTO EMPLOYEE VALUES ( 1 , 'ERIK ', 'BERGEN ' );
INSERT INTO EMPLOYEE VALUES ( 2 , 'LISE ', 'OSLO ' );

I would have defined empno as numeric therefore the surrounding quotes
are not needed in the INSERT statement.

Good luck,
Duane

[Juan Lanus]

If the table has not many columns may be you can write:

SELECT 'Insert into employee(', empno, ',', name, ',', address ')'

to get the example statement you wrote only without the apostrophes
that delimit the strings (because I don't know how to escape then in
SQL)

Chau

Juan Lanus

[ileana...@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.

[ryane...@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

[AngocA]

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.

[AngocA]

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