Finding Changes in 2 Tables.

[itsmarkdavies]

Can anyone suggest some sql to discover the changes between 2 tables. The contents of the tables used to be identical, but I have deleted data from ONE of the tables. I need to find out what I have deleted.

I thought about using this :-

SELECT * FROM TABLE1 WHERE
TABLE1.FIELD1 NOT IN (SELECT FIELD1 FROM TABLE2) AND
TABLE1.FIELD2 NOT IN (SELECT FIELD2 FROM TABLE2) AND TABLE1.FIELD3 NOT IN (SELECT FIELD3 FROM TABLE2)
...etc

but I cannot be sure it is working properly due to the vast number of records. Any ideas ? Thanks.

 

[tlbroadbent]

You might want to use something like this to find records that exist in Table1 but not in Table2. Limit the number of columns in the Where clause to columns that uniquely identify a record.

Select *
From Table1 As t1
Where Not Exists
(Select * From Table2
Where field1=t1.field1
And field2=t1.field2
And field3=t1.field4) Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.