SQLTeam.com | Weblogs | Forums

Finding matches

Hi There,
I have two databases with exactly the same customer datatables which hold names & address but each datatable may have the same customer details but with a different customerId.
I need to compare these tables and get a list of customers that are not in the newest database ignoring the id.
is there a way for me to do that.

Thanks for any help.

Best Regards,
Steve.

The direct method would be as shown below. It is likely to be slow if you have a lot of records in the table and/or a lot of columns. If the performance is unacceptable, you can try a checksum comparison, but this is the first thing I would try.

SELECT *
FROM
    DatabaseA.dbo.TableA AS a
WHERE
    NOT EXISTS
    (
        SELECT *
        FROM DatabaseB.dbo.TableB AS b
        WHERE
            a.lastname = b.lastname
            AND a.firstname = b.firstname
            AND a.streetaddress = b.streetaddress
            -- AND so on all columns that you want to compare
            -- except the customerid colum
    )
1 Like

SELECT * ---- All columns that you want to compare except the ID column
FROM OlderDatabase.dbo.Customers

EXCEPT

SELECT * ---- All columns that you want to compare except the ID column
FROM NewerDatabase.dbo.Customers

Hi There,
I have tried the suggestions but I get this error:
"Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation."
Would anyone know how I get over this?
Thanks

Best Regards,
Steve.

Force the collation to one or the other, for example as shown below. Collation applies only to character columns, so if you have non-character columns (such as integers, bits etc.) don't try to force any collation on those columns.

SELECT *
FROM
    DatabaseA.dbo.TableA AS a
WHERE
    NOT EXISTS
    (
        SELECT *
        FROM DatabaseB.dbo.TableB AS b
        WHERE
            a.lastname COLLATE SQL_Latin1_General_CP1_CI_AS = b.lastname COLLATE SQL_Latin1_General_CP1_CI_AS
            AND a.firstname  COLLATE SQL_Latin1_General_CP1_CI_AS = b.firstname  COLLATE SQL_Latin1_General_CP1_CI_AS 
            AND a.streetaddress  COLLATE SQL_Latin1_General_CP1_CI_AS = b.streetaddress  COLLATE SQL_Latin1_General_CP1_CI_AS 
            -- AND so on all columns that you want to compare
            -- except the customerid colum
    )