DBA Tips Archive for Oracle

  


Build Insert Statements for the Existing Data in Tables

by Ashish Kumar <kumara@jagat.com>

This script builds insert statements for the existing data in the tables. One can run the generated script to repopulate the data.

-- By: Ashish Kumar
-- Date Created: 10/01/2001
-- EMail: kumara@jagat.com
-- Code Version: 1.0.1

-- Objective:
-- You can use the following code to extract the existing data from tables in the form
-- of insert statements.  The generated script can be run at a later time to re-create your data.
-- This code is no match for EXPORT and IMPORT utilities.
-- Use it for *quick and dirty* situations.
-- The code handles only date, char, varchar2, and numeric data types.

-- Change History:

-- The example used in the code uses scott schema.

-- AUTHOR MAKES NO WARRANTIES FOR THIS CODE.

-- Step 1: Create this procedure:
create or replace Function ExtractData(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 user_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 user_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

-- STEP 2: Run the following code to extract the data.
set head off
set pages 0
set trims on
set lines 2000
set feed off
set echo off
var retline varchar2(4000)
spool c:\t1.sql
select 'set echo off' from dual;
select 'spool c:\recreatedata.sql' from dual;
select 'select ''-- This data was extracted on ''||to_char(sysdate,''mm/dd/yyyy hh24:mi'') from dual;' from dual;

-- Repeat the following two lines as many times as tables you want to extract
exec :retline:=ExtractData('dept');
print :retline;

exec :retline:=ExtractData('emp');
print :retline;

select 'spool off' from dual;
spool off
@c:\t1

-- STEP3: Run the spooled output c:\recreatedata.sql to recreate data.


Copyright (c) 1998-2017 Jeffrey M. Hunter. All rights reserved.

All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

Last modified on
Friday, 16-Jun-2000 00:00:00 EDT
Page Count: 30969