Re: [Q]generate insert statements from a table

From: <david_h_thompson_at_my-deja.com>
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

Original text of this message