The Oracle (tm) Users' Co-Operative FAQ

How can I write a script to output a table as a flat file which is a list of "insert into tableX(....) values (...)" statements?


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?

Back to index of questions


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


Back to top

Back to index of questions