Export partially from DB1 to DB2 with "SELECT" and "INSERT INTO" statements | other ideas?

Toad for Oracle
1

export partially from DB1 to DB2 with “SELECT” and
“INSERT INTO” statements | other ideas?

2

Hi Sven,

I need to export only 3 columns from two tables. See SQL statement. I
do this with generating "INSERT INTO" statements with the actual data,
copy the output to the new database (there is no direct network
connectivity) system and run the statements.

This works fine. But is there a more elegant way doing this? How would
you to the same?

Run the query in the editor and get the first page of results - if
paging occurs.

Right click the results grid. Select export dataset.

Set the export format = Insert Statements.

Set the destination to a file, or clipboard as desired.

Turn on "include set define off".

Turn on "include null text".

Turn off everything in the second column of options, but most
importantly, "include schema name".

Set commit interval to 0.

You may wish to turn off "display all results in grid" if you have lots
of data. Otherwise the results grid will fill up with every row
possible. Leave it off and only the first page is shown in the grid -
but the export does all the rows.

Click OK and you are done.

BEWARE: There will be a commit at the bottom of the script generated. I
don't see an option to prevent this from being generated. You may not
want to commit "automatically".

--
Cheers,
Norm. [TeamT]

3

On Wed, Feb 23, 2011 at 9:28 AM, Norman Dunbar wrote:

Right click the results grid. Select export dataset.

Set the export format = Insert Statements.

Set the destination to a file, or clipboard as desired.

Turn on "include set define off".

Turn on "include null text".

Turn off everything in the second column of options, but most
importantly, "include schema name".

Set commit interval to 0.

You may wish to turn off "display all results in grid" if you have lots
of data. Otherwise the results grid will fill up with every row
possible. Leave it off and only the first page is shown in the grid -
but the export does all the rows.

Click OK and you are done.

thanks Norman. Much simpler than generating "INSERT INTO" myself.

cheers Sven