I got a big set of data from combining multiple tables. Is there a way to export this data to insert statements? When I use the insert into detect automatically it complains no table name specified. When I specify a table name it uses that one table name for everything. It seems to forget which table the record came from.
So, depending on where you want to direct the output, you're going to have to specify either an output file (to create the insert script) or the schema.tableName that you want to insert into, assuming the table exists already and has the same columns/attributes that you're exporting.
I got a big set of data from combining multiple tables. Is there a way to export this data to insert statements?
yes, you seem to have found it.
When I use the insert into detect automatically it complains no table name specified.
It looks like from the screen shot that Toad was not able to detect a table name from your SQL.
When I specify a table name it uses that one table name for everything. It seems to forget which table the record came from.
It sounds like you are doing something like
select * from table1
union all
select * from table2
etc.
...then expecting Toad to determine from which table each record in the result came. That is not possible. The "Schema" and "Table" fields in that dialog are just for specifying which table to use in the insert statements (for all rows, as you found)
Yes that is correct. Any chance this feature could be added to toad?
No, it's not possible to tell from a row in a query result which table it came from (a result in a row could come from multiple tables or none at all), but there are other ways to export from multiple tables.
Go to automation designer
Select "App1" on the left, then double-click the "Export Dataset" action to add it to App1
Double-click the Export Dataset action to edit it.
Click on the dataset tab and add multiple SELECT statements, one for each table, ending each with a semicolon.
Go back to the Options tab and set the following options
Then click "Run" at the bottom. The dialog will disappear and file will be created.
You can run it again by right-clicking on the action and choosing "run"
