Re: [Q]generate insert statements from a table
Date: Mon, 21 Jun 1999 15:29:21 GMT
Message-ID: <7kllo2$d2u$1_at_nnrp1.deja.com>
In article <87n1xwu127.fsf_at_ararat.mygus.com>,
Vladik <reason_at_shadow.net> wrote:
> Hello, is there a tool (besides TOAD) or a C++/Java library that can
> generate insert statements with the data from a given
> table.
>
> Thanks in advance,
> Vladislav
>
Here is a SQL*Plus script you can use which generates INSERT statements to the standard output from data within a specified table.
set echo on
Rem
Rem BLDINS.SQL - Build Insert Statments
Rem
Rem This script may be useful when a quick partial dump of data is
needed for testing purposes.
Rem
Rem It will itself create a script which may then be invoked to create
insert statements
Rem for the given table(s). You must be logged on as the owner of the
table(s). This user
Rem must also have access to the Standard package owned by System. At
the end of the process
Rem the editor will be invoked to make any changes to the spooled
script.
Rem
set echo off
set serveroutput on size 900000
set define on
set heading off
set feedback off
select 'Current User: ' || user
from dual
;
Prompt
Prompt Would you like to see a list of Tables for the given user ?
accept list_fl prompt '(Y or N): '
set heading on
set verify off
set pagesize 60
col table_name format a50 heading 'List of Tables' print
select table_name
from user_tables
where upper(nvl('&list_fl', 'N')) = 'Y'
union
select 'No Tables Exist for ' || user table_name
from dual
where
nvl(upper('&list_fl'), 'N') = 'Y' and not exists
(select 'x' from user_tables )
;
set linesize 80
set pagesize 0
set heading off
set verify off
set feedback off
set flush off
Prompt
Prompt Enter the Table Name to build Insert Statements or blank for all
Tables
accept entered_table Prompt 'Table Name: '
Prompt
Prompt Do you wish to see the column names in the insert statements (Y
or N) ?
accept ins_stmt prompt '(Y or N): '
Prompt
Prompt Enter the name of the *.LST to spool.
Prompt (DO NOT enter the Drive letter or ".LST").
accept spool_file prompt 'Spool File: '
Prompt
Prompt Building the Create Insert Statement Script... Please Wait...
Prompt
spool &spool_file..sql
declare
cursor c1 is
select table_name
from user_tables
where table_name = nvl(upper('&entered_table'), table_name)
order by
table_name
;
cursor c2 (in_table_name in varchar2) is select column_id
,column_name ,data_type
from user_tab_columns
where table_name = in_table_name
order by
column_id
;
begin
dbms_output.put_line('Prompt Rem');
dbms_output.put_line('Prompt Rem Current User: ' || user);
-- -- Loop Thru Requested Table(s) --
for c1rec in c1
loop
-- -- Chr(39) = ASCII quote --
dbms_output.put_line('Prompt Rem');
dbms_output.put_line('Prompt Rem Insert Statments for Table: ' || c1rec.table_name);
dbms_output.put_line('Prompt Rem');
--
dbms_output.put_line('select ' || chr(39) || 'insert into ' ||
rtrim(c1rec.table_name) || chr(39)); --
- Display columns in the Insert Statements if requested
--
if nvl(upper('&ins_stmt'),'Y') = 'Y' then
dbms_output.put_line('|| ' || chr(39) || ' (' || chr(39));
--
- Loop Thru Column Names -- for c2rec in c2 (c1rec.table_name) loop if c2rec.column_id = 1 then dbms_output.put_line('||' || chr(39) || c2rec.column_name || chr(39)); else dbms_output.put_line(',' || chr(39) || ', ' ||c2rec.column_name || chr(39)); end if; end loop; -- c2 -- dbms_output.put_line('|| ' || chr(39) || ')' || chr(39) ); end if; -- dbms_output.put_line('|| ' || chr(39) || ' values (' || chr(39) ); --
- Loop Thru Column Names for Values -- for c2rec in c2 (c1rec.table_name) loop if c2rec.column_id = 1 then if c2rec.data_type = 'VARCHAR2' THEN dbms_output.put_line('|| chr(39) || ' || 'REPLACE('||rtrim (c2rec.column_name)||',chr(39),chr(39)||chr(39))' || ' || chr(39)'); else dbms_output.put_line('|| chr(39) || ' || rtrim (c2rec.column_name) || ' || chr(39)'); end if; else if c2rec.data_type = 'VARCHAR2' THEN dbms_output.put_line('|| ' || chr(39) || ',' || chr(39) || '|| chr(39) || ' || 'REPLACE('||rtrim(c2rec.column_name)||',chr (39),chr(39)||chr(39))' || ' || chr(39)'); else dbms_output.put_line('|| ' || chr(39) || ',' || chr(39) || '|| chr(39) || ' || rtrim(c2rec.column_name) || ' || chr(39)'); end if; end if; end loop; -- c2 -- dbms_output.put_line('|| ' || chr(39) || ');' || chr(39)); dbms_output.put_line('from ' || c1rec.table_name); dbms_output.put_line('-- Insert necessary Where and/or Order By clause(s) here'); dbms_output.put_line(';'); -- end loop; -- c1 --
end;
/
spool off
set serveroutput off
set linesize 80
set pagesize 14
set heading on
set verify on
set feedback on
set flush on
rem edit &spool_file
Prompt
Prompt The output has been spooled to &spool_file..sql
Prompt
Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.
Received on Mon Jun 21 1999 - 17:29:21 CEST