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.

Answer 1

Hi

Yes look at Oracle sql developer.Its free can be downloaded from otn.oracle.com

Answer 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;

Answer 3

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.

Answer 4

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

http://www.cxtools.net/default.aspx?nav=cxortl

• 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

Answer 5

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;

Answer 6

In a pinch, using string contatenation works great for smaller statements you want to build:

Select
    'Insert Into MyOtherTableTable Values(''' || MyMainTableColumn1 || ''' and ''' || MyMainTableColumn2 || ''')'
From MyMainTable