views:

2227

answers:

6

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.

+1  A: 

Hi

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

Unfortunately we are not allowed to use this. I'm stuck with trying to come up with a long hand solution. When I have time...
That's ok...it's a buggy turd IMHO anyways. {-o)
Boydski
+3  A: 

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;
Dave Costa
Thanks, but I'm grabbing the data from one database and moving it to another.
You could create a database link and do the query against the remote table.
Dave Costa
Thanks Dave. Do you have any suggestions on how to do this? SQL Station is old and crappy. However it is all we are allowed to use.
Well, probably you'd need to ask your DBA to do it, but you can try it. I don't know SQL Station, but I assume it allows you to execute any SQL.If both databases are Oracle: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_5005.htm#i2061505
Dave Costa
If the other database is not Oracle, start here: http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/ds_concepts.htm#sthref3857
Dave Costa
+2  A: 

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.

dpbradley
+2  A: 

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
Mark Harrison
A: 

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;

A: 

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
Boydski