Learn how to a build a cloud-first strategyRegister Now


Compare two MS SQL Tables for matches

Posted on 2010-01-06
Medium Priority
Last Modified: 2012-05-08
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.
Question by:megnin
  • 4
  • 3
LVL 11

Accepted Solution

Kelvin McDaniel earned 1600 total points
ID: 26191688
I believe the following is what you need:

    A1.keyApplicantID, A1.LastName, A1.FirstName, A1.SSN
    , (
        SELECT COUNT(*)
        FROM SYEP_BAK_09.dbo.Applicants AS A3
        WHERE A3.LastName = A1.LastName AND A3.FirstName = A1.FirstName AND A3.SSN = A1.SSN
      ) AS TotalCount
    SYEP.dbo.Applicants AS A1 INNER JOIN SYEP_BAK_09.dbo.Applicants AS A2
        ON A1.LastName = A2.LastName AND A1.FirstName = A2.FirstName AND A1.SSN = A2.SSN
LVL 60

Assisted Solution

by:Kevin Cross
Kevin Cross earned 400 total points
ID: 26191822
You can use a WHERE EXISTS if looking at existence by a primary key OR for the distinct / uniqueness of entire rows you can use INTERSECT.


Syntax would be :

select {column list} from tablea
select {column list}  from tableb


Author Comment

ID: 26191833
That's a beautiful thing!  Exactly what I need.
Something I noticed when I was experimenting earlier was that in Microsoft SQL Server Management Studio if I have the database (SYEP2007) selected as the default and I reference a table the same database by the full SYEP2007.dbo.Applicants "path" it says that it's an invalid table name.  I just call it "Applicants" and it works just fine though.
Thank you so much that was perfect!
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!


Author Comment

ID: 26191854
mwvisa1, azarc3 beat you to the punch.  ;-)
LVL 60

Expert Comment

by:Kevin Cross
ID: 26191877
Sorry, I just saw the "how many times" piece which makes the first posted suggestion make more sense to me.  That subquery should be fine, but since you have to do a JOIN anyway, I would also consider like this:

SELECT a.keyApplicantID, a.LastName, a.FirstName, a.SSN
    , b.TotalCount
FROM SYEP.dbo.Applicants a
   SELECT SSN, COUNT(*) AS TotalCount
   FROM SYEP_BAK_09.dbo.Applicants
) b ON a.SSN = b.SSN

Hope that helps!

Author Closing Comment

ID: 31673524
azarc3, your solution was just what I needed.

mwvisa1, thank you for your suggestion.  I will likely make use of that technique somewhere.

Author Comment

ID: 26191906
Yes, that makes sense as well.  Thank you again.
LVL 60

Expert Comment

by:Kevin Cross
ID: 26191955
You are most welcome!
@azarc3, good work...

Best regards and happy coding,

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question