Insert statement generator

This script generates INSERT statements from an existing table that can be used as a backup or source script for future data loads.

This Content Component encountered an error

I have seen many DBAs asking for a script that generates "INSERT into values...." statements from an existing table's data that could be used as a backup or source script for a future data load. So I thought why not write one for the benefit of everybody. It has been tested on Oracle 7/8i/9i.

This script is dynamic and very user-interactive. When run, it will ask for values for the parameters and then opens up a result file for the user. If the table is very large, WHERE conditions can be included in v_str6.

set echo off

REM This script should be run as SYSTEM/SYS ONLY.
REM This script works for tables with columns of datatypes CHAR,VARCHAR2,NUMBER & DATE ONLY.

set feed off
SET TERMOUT ON
ACCEPT own CHAR prompt 'Enter Value For Schema Name  :<>: '; 
ACCEPT tn CHAR prompt 'Enter Value For Table Name  :<>: '; 
set ver off
 create table ins_cols(owner,tname,colid,colname,datatype) 
 storage(initial 200k next 200k maxextents 512)
 as select owner,table_name,column_id,column_name,data_type from 
 dba_tab_columns where table_name=upper('&tn') and owner=upper('&own')
/
create table ins_text (lineno NUMBER,text varchar2(4000))
storage(initial 200k next 200k maxextents 512)
/

declare
 cursor c1 is select owner,tname,colid,colname,datatype from ins_cols
 order by colid;
 v_colid  number;
 v_maxcolid number;
 v_line  number:=1;
 v_colname varchar2(4000);
 v_tname  varchar2(100);
 v_datatype varchar2(100);
 v_owner  varchar2(100);
 v_str  varchar2(4000);
 v_str1  varchar2(4000);
 v_str2  varchar2(4000);
 v_str3  varchar2(4000);
 v_str4  varchar2(4000);
 v_str5  varchar2(4000);
 v_str6  varchar2(4000);
 
 procedure write_out(p_line INTEGER,p_str VARCHAR2) is
    begin
         insert into ins_text (lineno,text) values (p_line,p_str);
  commit;
    end;
begin
 select max(colid) into v_maxcolid from ins_cols;
 open c1;
 loop
 fetch c1 into v_owner,v_tname,v_colid,v_colname,v_datatype;
 exit when c1%notfound;

 if v_colid < v_maxcolid then 
  v_str:=v_colname||',';
  v_str1:=v_str1||v_str;
 else  
  v_str:=v_colname||')';
  v_str1:=v_str1||v_str;
 end if;
 end loop;
 close c1;
 v_str2:='select '||chr(39)||'insert into '||v_owner||'.'||v_tname||'('||v_str1||' VALUES ('||CHR(39)||',';

 write_out(v_line,v_str2);
 v_line:=v_line+1;
 
 open c1;
 loop
 fetch c1 into v_owner,v_tname,v_colid,v_colname,v_datatype;
 exit when c1%notfound;
 if v_colid < v_maxcolid then 
  
  if instr(v_datatype,'CHAR')>0 then 
                        v_str3:='decode('||v_colname||','||''''''||','||chr(39)
   ||'NULL'||chr(39)||','||'chr(39)||'||'TRIM('||v_colname||')||chr(39)'||')||'
   ||'chr(44)||';

   write_out(v_line,v_str3);
   v_line:=v_line+1;

      elsif v_datatype='NUMBER' then 
         v_str4:='NVL('||'TO_CHAR('||v_colname||')'||','||CHR(39)
   ||'NULL'||CHR(39)||')'||'||chr(44)||'; 

   write_out(v_line,v_str4);
   v_line:=v_line+1;

      elsif v_datatype='DATE' then 
                        v_str5:='decode('||v_colname||','||''''''||','||chr(39)||'NULL'||chr(39)||','||chr(39)
||'TO_Date('||chr(39)||'||'||'chr(39)||'||'TO_Char('||v_colname||','||'''DD-MON-YYYY HH:MI:SS AM'''||')'
   ||'||chr(39)||'||CHR(39)||','||CHR(39)||'||'||CHR(39)||'''''DD-MON-YYYY HH:MI:SS AM'''''
   ||CHR(39)||'||'||CHR(39)||')'||CHR(39)||')||'||'chr(44)||';

   write_out(v_line,v_str5);
   v_line:=v_line+1;

  end if;
 else
  if instr(v_datatype,'CHAR')>0 then 
                        v_str3:='decode('||v_colname||','||''''''||','||chr(39)
   ||'NULL'||chr(39)||','||'chr(39)||'||'TRIM('||v_colname||')||chr(39)'||')||'
   ||''');''';

   write_out(v_line,v_str3);
   v_line:=v_line+1;

      elsif v_datatype='NUMBER' then 
         v_str4:='NVL('||'TO_CHAR('||v_colname||')'||','||CHR(39)
   ||'NULL'||CHR(39)||')'||'||'||''');'''; 

   write_out(v_line,v_str4);
   v_line:=v_line+1;

      elsif v_datatype='DATE' then 
                        v_str5:='decode('||v_colname||','||''''''||','||chr(39)||'NULL'||chr(39)||','||chr(39)
||'TO_Date('||chr(39)||'||'||'chr(39)||'
   ||'TO_Char('||v_colname||','||'''DD-MON-YYYY HH:MI:SS AM'''||')'
   ||'||chr(39)||'||CHR(39)||','||CHR(39)||'||'||CHR(39)||'''''DD-MON-YYYY HH:MI:SS AM'''''
   ||CHR(39)||'||'||CHR(39)||')'||CHR(39)||')||'||''');''';

   write_out(v_line,v_str5);
   v_line:=v_line+1;

  end if;
  
 end if;
 end loop;
   select distinct owner,tname into v_owner,v_tname from ins_cols;
   v_str6:='from '||v_owner||'.'||v_tname||';'; 

   write_out(v_line,v_str6);
   v_line:=v_line+1;

 close c1;

end; 
/

set termout off
set linesize 2000
set head off
set trimspool on
set pages 2000
spool Ins
 select text from ins_text order by lineno;
spool off
spool Insert
@ins.lst
spool off

DROP TABLE ins_cols
/
DROP TABLE ins_text
/
set echo on
set termout on
set feed on
set ver on
set head on
set feed on
set linesize 80
ed Insert.lst

For More Information

  • Feedback: E-mail the editor with your thoughts about this tip.
  • More tips: Hundreds of free Oracle tips and scripts.
  • Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
  • Ask the Experts: Our applications, SQL, database administration, and data warehousing gurus are waiting to answer your toughest questions.
  • Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
  • Best Web Links: Oracle tips, tutorials, and scripts from around the Web.

This was first published in August 2003

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close