Toad World® Forums

Compare Data - Solving dependencies

Hi,
I have a Compare Data automation script that copies data from one Schema to another. The data is composed of several tables that have dependencies between them.
When I run the resulting script, it will allways fail, as the DELETE of the parent records and the INSERT of the child records can only be done in a specific order.

In order for this to work, the INSERTS have to be done top down and the DELETES have to be done bottom up.

The example at hand is three tables for ENUMS, ENUM_VALUE that have a FK to ENUMS and ENUM_LABLE that have a FK to ENUM_VALUE.

I started by creating three compare data scripts that save the results to 01ENUMS.SQL, 02ENUM_VALUE.sql and 03ENUM_VALUE.sql

At the beginning of each file there is
SET CURRENT SQLID = '' and
SET CURRENT SCHEMA . . . .

Find SET CURRENT SQLID
replace with --BEGINFILESTUB; SET CURRENT SQLID ( I don't use this line in my schemas, you might have to change this if you do need this line. Maybe Replace to /BEGINFILESTUB/; SET CURRENT SQLID).

I save the result to three new files. Let's call them 1.SQL, 2.SQL, and 3.SQL. This way the process is re-usable for any three tables that have to be compared and updated in this way.

I do a Find/Replace
Source file: 3.sql
Find: --BEGINFILESTUB
Replace with: "FILE CONTENTS" ( whic is a button right over the Set Value Combo).
Set value: 2.sql.

I save the file to COMPARE_INSERTS.sql.

I do a Find/Replace
Source file: COMPARE_INSERTS.sql.l
Find: --BEGINFILESTUB
Replace with: "FILE CONTENTS" ( whic is a button right over the Set Value Combo).
Set value: 1.SQL.
Overwrite file checked for yes.

I save the file to COMPARE_INSERTS.sql.

I do a Find/Replace
Source file: COMPARE_INSERTS.sql.l
Find: DELETE
Replace with: "VALUE" ( whic is a button right over the Set Value Combo).
Set value: -- DELETE
Overwrite file checked for yes.

I save the file to COMPARE_INSERTS.sql.

Now I have one file that does the Inserts is the specific odred that I need.

To create a single file that DELETES, I do the same process in the opposite order saving the file as COMPARE_DELETES.
I have to add another Replace

I do a Find/Replace
Source file: COMPARE_DELETESsql.l
Find: VALUES
Replace with: "VALUES" ( whic is a button right over the Set Value Combo).
Set value: -- VALUES
Overwrite file checked for yes.

Shimon J.