| Author's name: Michael Eichler Author's Email:meichler@proteus.com |
Date written: 4th March 2002 Oracle version(s): 8.1 |
| How can I write a script to output a table as a flat file which is a list of "insert into tableX(....) values (...)" statements? | |
Try the following, in sqlplus
SQL> set trimspool on
SQL> set pagesize 0
SQL> set heading off
SQL> set feedback off
SQL> set termout off
SQL> spool file.txt
SQL> SELECT 'INSERT INTO TABLE_B (foo, bar) VALUES (' || foo || ', ' || bar || ');' FROM TABLE_A;
SQL> spool off
SQL> exit
It should result in a file called file.txt with
one row for each row returned by the select statement.
If your rows contain strings, you'll need to quote them, like...
... VALUES (''', || foo || ''', ''' || bar || ''');' FROM ...
If your strings contain single quotes, you'll also need to pass them through the REPLACE function to escape them, like...
... || REPLACE(foo, ''', '''') || ...
.
Further reading: N/A