Compare two MS SQL Tables for matches
Posted on 2010-01-06
I need to compare two MS SQL 2005 tables to find records in table 1 that exist in table 2.
Table 1 was the original table in the original database.
I backed up Table 1, deleted all the records then started adding new records with no change to the structure.
Table 2 is in a new database I just created and restored the backup from the original database.
The two tables I want to compare are identical in structure. Table 2 has three years worth of old personnel records with key id numbers that begin with the year as 200800001 or 200900001. Table 1 has only this years records and key ids start with the year as 201000001. That's not really important though except that "John Smith" with Employee Number "12345" may be in the old database more than once if he was here for more than one year, e.g. 200800056-John Smith-12345 and 200900081-John Smith-12345 are the same person with two records in the old database.
I need to compare Table 1 (the new one) with Table 2 and list all the people who are in Table 1 who are also in Table 2. I only need to compare FirstName, LastName and SSN although just SSN may suffice.
I don't know if this could be done at the same time or not, but I need to know if "John Smith" from Table 1 is not only in Table 2, but how many times (just looking at name and SSN fields or just SSN).
SYEP.dbo.Applicants is Table 1
SYEP_BAK_09.dbo.Applicants is Table 2
LastName, FirstName and SSN are the relevant fields
keyApplicantID is the primary key field (200800001..., 200900001... in Table 2 and 201000001... in Table1) perhaps not relevant.
The purpose of the query is so that I can list the people in this year's program (Table 1 - 201000000s) who participated in at least two previous years (Table 2 - 200800000s and 200900000s)
Thank you very much in advance for your assistance.