Insert Missing Data

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 Received on Thu Nov 01 2001 - 22:24:34 CST