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, [email protected] 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)

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)

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)

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)