Compare two MS SQL Tables for matches

Posted on 2010-01-06
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
    LVL 11

    Accepted Solution

    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 59

    Assisted Solution

    by:Kevin Cross
    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

    LVL 1

    Author Comment

    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!
    LVL 1

    Author Comment

    mwvisa1, azarc3 beat you to the punch.  ;-)
    LVL 59

    Expert Comment

    by:Kevin Cross
    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!
    LVL 1

    Author Closing Comment

    azarc3, your solution was just what I needed.

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

    Author Comment

    Yes, that makes sense as well.  Thank you again.
    LVL 59

    Expert Comment

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

    Best regards and happy coding,

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    761 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

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now