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.

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 last published in August 2003

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close