Home › PL/SQL code to generate Oracle insert statements from existing table data

PL/SQL code to generate Oracle insert statements from existing table data 

Dr. Xi
Joined:
04/09/2007
Posts:
565

October 09, 2009 19:27:22    Last update: October 09, 2009 19:30:01
  1. PL/SQL code from Pandazen: 
    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;
/
SHOW ERRORS


    Usage:
    • To create the script for generating the INSERT statements: 
      set head off
set pages 0
set trims on
set lines 2000
set feed off
set echo off
spool genInsertForMyTable.sql
SELECT get_insert_script('MY_TABLE') FROM DUAL;
spool off


    • Run the resulting script to generate the INSERT script: 
      set pages 0
set trims on
set lines 2000
set feed off
set echo off
spool MY_TABLE_INSERT.sql
@genInsertForMyTable.sql
spool off



  2. PL/SQL code from Oracle Ask Tom 
    set serveroutput on size 100000
set feedback off

declare
  v_table_name      varchar2(30) := 'EMP';  -- Your Tablename
  v_column_list     varchar2(2000);
  v_insert_list     varchar2(2000);
  v_ref_cur_columns varchar2(4000);
  v_ref_cur_query   varchar2(2000);
  v_ref_cur_output  varchar2(2000);
  v_column_name     varchar2(2000);
  cursor c1 is select column_name, data_type from user_tab_columns where table_name = v_table_name 
order by column_id;
  refcur            sys_refcursor; 
begin
  for i in c1 loop
     v_column_list := v_column_list||','||i.column_name;
     if i.data_type = 'NUMBER' then
        v_column_name := i.column_name;
     elsif i.data_type = 'DATE' then
        v_column_name := 
chr(39)||'to_date('||chr(39)||'||chr(39)'||'||to_char('||i.column_name||','||chr(39)||'dd/mm/yyyy 
hh:mi:ss'||chr(39)||')||chr(39)||'||chr(39)||', '||chr(39)||'||chr(39)||'||chr(39)||'dd/mm/rrrr 
hh:mi:ss'||chr(39)||'||chr(39)||'||chr(39)||')'||chr(39);
     elsif i.data_type = 'VARCHAR2' then
        v_column_name := 'chr(39)||'||i.column_name||'||chr(39)';
     end if;
     v_ref_cur_columns := v_ref_cur_columns||'||'||chr(39)||','||chr(39)||'||'||v_column_name;
  end loop; 
  v_column_list     := ltrim(v_column_list,',');
  v_ref_cur_columns := substr(v_ref_cur_columns,8);

  v_insert_list     := 'INSERT INTO '||v_table_name||' ('||v_column_list||') VALUES ';
  v_ref_cur_query   := 'SELECT '||v_ref_cur_columns||' FROM '||v_table_name;
  
  open refcur for v_ref_cur_query;
  loop
  fetch refcur into v_ref_cur_output; 
  exit when refcur%notfound;
    v_ref_cur_output := '('||v_ref_cur_output||');'; 
    v_ref_cur_output := replace(v_ref_cur_output,',,',',null,');
    v_ref_cur_output := replace(v_ref_cur_output,'(,','(null,');
    v_ref_cur_output := replace(v_ref_cur_output,',,)',',null)');
    v_ref_cur_output := replace(v_ref_cur_output,'null,)','null,null)');
    v_ref_cur_output := v_insert_list||v_ref_cur_output; 
    dbms_output.put_line (v_ref_cur_output); 
  end loop; 
end;
/


[ Comment  | Tags ]
 
Easy email testing with http://www.ximailstop.com

Related Notes