Finding matches

Transact-SQL
1

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.

2

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
3

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

4

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.

5

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
    )
1 Like