Build Insert Statements for the Existing Data in Tables
下面这个脚本实现了取得一个非空表中的所有insert语句
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 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 /home/oracle/hxy/t1.sql select ‘set echo off‘ from dual; select ‘spool /home/oracle/hxy/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 @/home/oracle/hxy/t1
-- STEP3: Run the spooled output c:\recreatedata.sql to recreate data. 查看recreatedata.sql
例如：
db111@dbrac1 /home/oracle/hxy$ cat recreatedata.sql
-- This data was extracted on 03/11/2014 21:42 insert into DEPT ( DEPTNO, DNAME, LOC) values (10, ‘ACCOUNTING‘, ‘NEW YORK‘); insert into DEPT ( DEPTNO, DNAME, LOC) values (20, ‘RESEARCH‘, ‘DALLAS‘); insert into DEPT ( DEPTNO, DNAME, LOC) values (30, ‘SALES‘, ‘CHICAGO‘); insert into DEPT ( DEPTNO, DNAME, LOC) values (40, ‘OPERATIONS‘, ‘BOSTON‘); -- commit; insert into EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7369, ‘SMITH‘, ‘CLERK‘, 7902, to_date(‘12/17/1980 00:00‘,‘mm/dd/yyyy hh24:mi‘), 800, Null, 20); insert into EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7499, ‘ALLEN‘, ‘SALESMAN‘, 7698, to_date(‘02/20/1981 00:00‘,‘mm/dd/yyyy hh24:mi‘), 1600, 300, 30); insert into EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7521, ‘WARD‘, ‘SALESMAN‘, 7698, to_date(‘02/22/1981 00:00‘,‘mm/dd/yyyy hh24:mi‘), 1250, 500, 30); insert into EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7566, ‘JONES‘, ‘MANAGER‘, 7839, to_date(‘04/02/1981 00:00‘,‘mm/dd/yyyy hh24:mi‘), 2975, Null, 20);
取得表中数据的insert语句,布布扣,bubuko.com
原文：http://www.cnblogs.com/haoxiaoyu/p/3594983.html