Toad World® Forums

copy data to another schema does not generate a script as in 10.1

Hi people,

I am new to toad.

Can you please help me out here.

In toad 10.1 to copy multiple tables to a different schema . there was an option to save the script and re run it .

which was very useful to the work i do. but i dont see the option in toad 12.6 (i.e i cannot save a script for re use)

sample script that i could generate and reuse:

You can run the data copy utility from a command line

using this file with a few modifications.

Anything after a ‘#’ in any line will be ignored as a comment.

Calling TOAD like this will perform a data copy from command line :

“M:\WINPROG\TOAD10\Toad.exe” -c “DZ445/<8,0,0,0,209,188,70,199,131,47,99,152,32,114,182,193,112,224,0,168>@TCS.WORLD” DATACOPY="\res09\ndrive\ol230.OAAD\Gourav\Client Query\Helmut\14-May-2013\expsecurty.txt"

(obviously, your path to TOAD may differ, along with your connect info.

The file name after “DATACOPY=” is the name of this file.)

Uncomment the following line if you wish to close TOAD when the data copy is complete.

#CloseTOAD

You may edit this file, but you must observe the following rules:

1) The “DestSchemaName” line should be commented out if source tables

are from multiple schemas. It should NOT be commented out (ie, it

is required) if source tables are all from one schema.

2) If required, “DestSchemaName” should be specified AFTER connection info.

3) Line feeds in ‘where’ clauses must be represented by {NL}

Not following this line feed representation will cause part of

your ‘where’ clause to be ignored.

Connect Info…

CONNECTSRC=DZ445/<8,0,0,0,209,188,70,199,131,47,99,152,32,114,182,193,112,224,0,168>@TCS.WORLD
CONNECTDEST=TCS_SR2012/<10,0,0,0,172,129,144,68,86,209,130,204,107,0,171,3,236,86,66,48>@TCS_ERI2.DEV

Table listings…

TABLE=TCS_OWNER.AC_CUSTODIAN_MSTR
WHERECLAUSE[TCS_OWNER.AC_CUSTODIAN_MSTR]= where sec_id in (‘027339778’){NL}
TABLE=TCS_OWNER.AC_IDENTIFICATION
WHERECLAUSE[TCS_OWNER.AC_IDENTIFICATION]=where sec_id in (443893) {NL}
TABLE=TCS_OWNER.AC_SEC_HLDNGS_HSTRY
WHERECLAUSE[TCS_OWNER.AC_SEC_HLDNGS_HSTRY]= where secid in (‘027339778’){NL}
TABLE=TCS_OWNER.AC_SEC_MSTR
WHERECLAUSE[TCS_OWNER.AC_SEC_MSTR]= where sec_id in(443893) {NL}
TABLE=TCS_OWNER.SCRTY_ADDTNL_DTLS
WHERECLAUSE[TCS_OWNER.SCRTY_ADDTNL_DTLS]=where secid in(443893) {NL}

Other Settings…

DestSchemaName=TCS_SR2012
Truncate=N
Reuse=N
ArraySize=500
RollbackSegment=No Specific RBS
CommitOption=1

This and most of the other old command line features have been moved into the Automation Designer. Look for the toolbar icon that looks like a lightning bolt with a pencil next to it. Click it.

On the Import/Export tab, you’ll find an icon for “copy table data”. Click that and then click to drop it in the area below. Then you can set the properties on it and save the action for reuse there. Check the docs on how to schedule or run from command line.

If you still have questions, let us know.

Thanks a ton John exactly wat i needed.