Re: Generating SQL script for table filling

From: Kirmo Uusitalo <kirmo.uusitalo.NO.SPAM_at_iki.fi>
Date: Wed, 20 May 1998 10:17:49 GMT
Message-ID: <3564acf0.16209928@192.1.1.13>

On Tue, 19 May 1998 08:59:05 +0200, Jan Januska <J.Januska_at_sh.cvut.cz> wrote:

>Please,
>How do I get quickly (using some tool or script) a SQL script containing
>INSERT commands that fill table.
>Value to insert must be taken from current contain of the table in
>database.
>In other words, I want to export a table in SQL-like dump.
>
> Thanks for help, Jan Januska

Save the sql script below as cre_ins.sql. It should do the trick. I can't recall the original author of the file, but it was on Oracle newsgroups roughly a year ago. I have modified it a little to suit my needs.

HTH, Kirmo Uusitalo

rem usage: SQL> &cre_ins.sql owner table_name number_of_rows_to_insert

rem                                           -1 => all

set linesize 200
set pagesize 0
set heading off
set verify off
set feedback off

set termout off
spool temp.sql
PROMPT set pagesize 0
PROMPT set heading off
PROMPT set verify off
PROMPT set feedback off

PROMPT select 'INSERT INTO &1..&2
PROMPT (
SELECT DECODE(column_id,1,' ',',')||column_name FROM all_tab_columns
WHERE table_name = UPPER('&2')
and owner=upper('&1')
ORDER BY column_id
/
PROMPT )
PROMPT VALUES (
SELECT DECODE(column_id,1,' ',',')||

       DECODE(data_type,
              'VARCHAR2',''''''''||'||'||column_name||'||'||'''''''',
              'NUMBER'
,''''||'||DECODE('||column_name||',NULL,''NULL'','||column_name||')||'||'''',
              'DATE'

,'TO_DATE('''''''||'||'||'TO_CHAR('||column_name||',''DD-MON-YYYY HH:MI:SS AM'')'||'||'||''''''''||',''''DD-MON-YYYY HH:MI:SS AM'''')') FROM all_tab_columns
WHERE table_name = UPPER('&2')
and owner=upper('&1')
ORDER BY column_id
/
PROMPT )
PROMPT /'
PROMPT from &1..&2
select decode (&3,-1,'','where rownum <= &3') from dual; PROMPT /
PROMPT
PROMPT set pagesize 0

SPOOL OFF SPOOL &1._&2._imp.sql
PROMPT PROMPT Table is being filled.... PROMPT set feedback off
PROMPT
start temp.sql
PROMPT
PROMPT set feedback on
PROMPT SPOOL OFF set pagesize 24
set heading on
set verify off
set feedback on
set termout on

PROMPT Run &1._&2._imp.sql (in SQL*Plus) to load the table &1..&2 contents.

Kirmo.Uusitalo_at_iki.fi                   Key Technologies Oy 
System Analyst                          Uotinmaenkuja 9
tel. +358-9-323451,+358-500-439125      00970  HELSINKI
fax         324031                      FINLAND

Received on Wed May 20 1998 - 05:17:49 CDT