Is there an Oracle SQL tool that builds insert statements from a result set?

Question

Is there an Oracle SQL tool that builds insert statements from a result set? We are currently only allowed to use a tool called SQL Station. I'd like to either suggest a tool, like Rapid SQL or CrazySQuirrell, or build my own re-usable chunk of sql.

Solution 1

I found this solution, which is what I'm using now. Thanks for all of the help. It turns out we can use SQL+ too. For some reason I can't run it in SQL Station.

COPY FROM userid/password@from_DB TO userid/password>@to_DB INSERT toDB_tablename USING SELECT * FROM fromDB_tablename where ....;

commit;

Solution 2

Where is this result set coming from? If you mean that you want to execute a SELECT, then insert the resulting data into another table, you can do that in a single SQL statement:

INSERT INTO table2 (columnA, columnB)
  SELECT columnA, columnB
    FROM table1;

Solution 3

If you want command line tools, the free cx_OracleTools will do this, and some other nice things as well.

http://cx-oracletools.sourceforge.net/

• CompileSource - execute statements in a file, checking for errors
• CopyData - copy data from one table or view to another
• DbDebugger - allows simple debugging of PL/SQL
• DescribeObject - describe objects as SQL statements for recreation
• DescribeSchema - describe multiple objects as SQL statements for recreation
• DumpCSV - dump the results of a select statement as comma separated values
• DumpData - dump the results of a select statement as insert statements
• ExportColumn - dump the data from a column into a file
• ExportData - dump the data from a database into a portable dump file
• ExportObjects - describe object as SQL statements for recreation in files
• ExportXML - export data from a table into a simple XML file
• GeneratePatch - generate SQL script to go from one set of objects to another
• GenerateView - generate a view statement for a table
• ImportColumn - import the contents of a file into a column in the database
• ImportData - import the data dumped with ExportData
• ImportXML - import data from an XML file (such as those created by ExportXML)
• RebuildTable - generate SQL script to rebuild the table
• RecompileSource - recompile all invalid objects in the database

Solution 4

PL/SQL Developer will do this as well. I've used both PL/SQL Developer as well as Oracle's SQL Developer, and in my opinion PL/SQL Developer has a smoother and more consistent interface. Not sure about SQL Developer, but PL/SQL Dev. also lets you export result sets as CSV,XML, and HTML.

It also behaves OK under WINE if you're running Linux.

View more solutions