How to compare two tables?

[sabavno]

Hi,

I want to compare two tables record by record, find those records that are not in the Table2 and put them into the temporary table.

Please advise me on the best approach. (The tables are 70000 records each)

Thanks,

 

[KenReay]

hi

do you mean compare every column, or just the Prime Key?

If you mean just the prime key, then

INSERT INTO tblTemp SELECT * FROM tblOne WHERE PK NOT IN (SELECT PK FROM TblTwo);

would be well on the way to doing it Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK

 

[sabavno]

I tried to run this SQL statement on 70000 records table

DoCmd.RunSQL ("INSERT INTO tblEmpNonActive SELECT * FROM Employee WHERE EmployeeID NOT IN (SELECT Employee_Num FROM dbo_enterprise_t1)&quot


and was taking forever, I waited for about 25 minutes and then killed the procedure.

Is there more effective way to compare two tables?

 

[KenReay]

Hi

What specification PC are you using?

Anyway besides that, if you just want a lsit of records not present, you could make a query joining the two tables on prime key.

Right click on the join in design view and set to the appropriate value depending on which table to want to check for missing entries. Set the critieria to PK = Null on the 'other' table.

The SQL I sent you inserts into a third table, which will take longer.

I think teher is a wizard to guide you through this query anyway Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK

 

[GGleason]

You might want to try a query that searches only on differences on one field (if that is possible). If that is the case, the following sample query might speed things up:
[tt]
SELECT tblA.NameA
FROM tblA
WHERE not exists (select tblB.NameB from tblB where tblA.NameA = tblB.NameB)=true;
[/tt]
Otherwise, a non-Access solution would be to copy the two tables into text files and use a text comparison utility, such as Beyond Compare, to display the differences.

hth,
GGleason