Posted by: songbard | November 10, 2008

Create DML from tables eg. insert statements from all rows in a table

The following was tested on Oracle 11g.

1. Create a function as below

create or replace FUNCTION GET_INSERT_SCRIPT (V_TABLE_NAME VARCHAR2)
RETURN VARCHAR2 AS
B_FOUND BOOLEAN := FALSE;
V_TEMPA VARCHAR2 (8000);
V_TEMPB VARCHAR2 (8000);
V_TEMPC VARCHAR2 (255);
BEGIN
FOR TAB_REC IN (SELECT TABLE_NAME
FROM ALL_TABLES
WHERE TABLE_NAME = UPPER (V_TABLE_NAME)) LOOP
B_FOUND := TRUE;
V_TEMPA := ‘select ”insert into ‘ || TAB_REC.TABLE_NAME || ‘ (‘;

FOR COL_REC IN (SELECT *
FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME = TAB_REC.TABLE_NAME
ORDER BY COLUMN_ID) LOOP
IF COL_REC.COLUMN_ID = 1 THEN
V_TEMPA := V_TEMPA || ”’||chr(10)||”’;
ELSE
V_TEMPA := V_TEMPA || ‘,”||chr(10)||”’;
V_TEMPB := V_TEMPB || ‘,”||chr(10)||”’;
END IF;

V_TEMPA := V_TEMPA || COL_REC.COLUMN_NAME;

IF INSTR (COL_REC.DATA_TYPE, ‘CHAR’) > 0 THEN
V_TEMPC := ””””’||’ || COL_REC.COLUMN_NAME || ‘||””””’;
ELSIF INSTR (COL_REC.DATA_TYPE, ‘DATE’) > 0 THEN
V_TEMPC :=
”’to_date(”””||to_char(‘
|| COL_REC.COLUMN_NAME
|| ‘,”mm/dd/yyyy hh24:mi”)||”””,””mm/dd/yyyy hh24:mi””)”’;
ELSE
V_TEMPC := COL_REC.COLUMN_NAME;
END IF;

V_TEMPB :=
V_TEMPB
|| ”’||decode(‘
|| COL_REC.COLUMN_NAME
|| ‘,Null,”Null”,’
|| V_TEMPC
|| ‘)||”’;
END LOOP;

V_TEMPA :=
V_TEMPA
|| ‘) values (‘
|| V_TEMPB
|| ‘);” from ‘
|| TAB_REC.TABLE_NAME
|| ‘;’;
END LOOP;

IF NOT B_FOUND THEN
V_TEMPA := ‘– Table ‘ || V_TABLE_NAME || ‘ not found’;
ELSE
V_TEMPA := V_TEMPA || CHR (10) || ‘select ”– commit;” from dual;’;
END IF;

RETURN V_TEMPA;
END;

2. Call the function like

select get_insert_script(<tablename>) from dual

eg. select get_insert_script(‘hosts’).

I get a consolidated sql script like below :

select ‘insert into HOSTS (‘||chr(10)||’HOSTNAME,’||chr(10)||’SUPPORT_GROUP,’||chr(10)||’ESCALATION_GROUP) values (‘||decode(HOSTNAME,Null,’Null’,””||HOSTNAME||””)||’,’||chr(10)||”||decode(SUPPORT_GROUP,Null,’Null’,””||SUPPORT_GROUP||””)||’,’||chr(10)||”||decode(ESCALATION_GROUP,Null,’Null’,””||ESCALATION_GROUP||””)||’);’ from HOSTS;

Running the above script in sql prompt I get the insert statements for all the rows in ‘hosts’ table.

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: