Re: Insert Missing Data

On 1 Nov 2001 20:24:34 -0800, munzil_at_computan.on.ca (Munzil Qureshi) wrote:

>Hello all, I have 2 databases. 1 local database and 1 remote database.
>Both databases have exactly the same tables, relationships and data.
>However in the remote database(call it database A) some data has been
>deleted from 2 of the tables. The data deleted is for the year 2000.
>The data is still on the local database and I am trying to create a
>script .sql file that the user on the remote database can run by
>logging in to their database and run the script to insert the missing
>data into the 2 tables by selecting the data from the local database.
>
>The two tables are related to one another in a 1->many relationship
>from Table A to Table B(Note: the 2 tables are not related to the
>other tables so I don't have to worry about missing or invalid data).
>
>My current script looks like this:
>
>INSERT INTO Table_A(eem_id, eas_id, dgr_id)
>SELECT eem_id, eas_id, dgr_id
>FROM Table_A
>WHERE calendar_period like '2000%';
>
>Note: This works fine to insert data into the first table because the
>Primary Keys are NOT to be INSERTED manually because I have database
>triggers that handle that.
>This script works when the user logs on to the remote database and
>runs this script file.
>
>The problem is inserting the missing data into Table_B
>
>INSERT INTO Table_B(Table_A_ID, first_name, last_name)
>SELECT Table_A_Fk, first_name, last_name
>FROM Table_B, Table_A
>WHERE Table_A.id = Table_B.Table_A_ID
>AND Table_A.calendar_period like '2000%';
>
>Note: The reason that the insert into the second table does not work
>is because the foreign key from table A (ie: Table A's id) is
>mandatory, however the value
>for Table A's id is inserted automatically by triggers.
>
>Therefore, does anyone know how I can get the insert into the second
>table to work? I think that I must have to store the id for Table_A
>that is being inserted somehow, but I am not sure as to how this will
>work. If someone could please give me some insight, I would appreciate
>it greatly.
>
>Thanks,
>
>Munzil

How about adding
and not exists
(select 'x'
 from table_b
 where table_b.<some discriminating element> = table_a.<some discriminating elemt>)

It also looks like you are saying you have an artificial key on those tables, and the key generator is *not* in sync. That may be the source of your problems.

Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Fri Nov 02 2001 - 00:52:03 CST