Tip

Insert statement generator

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

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
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
Sort by: OldestNewest

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:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.