What is the best way to write a query to find a mismatch between 2 tables?

For instance, suppose you had 2 tables, each in a different database, but you wanted to make sure the addresses in each table were in sync.

Table 1 = Ship To Number (PK), Name, Address1, Address2, Address3, City, State, Zip
Table 2 = Ship To Number (PK), Name, Address1, Address2, Address3, City, State, Zip

What is the syntax to find where a difference occurs between the 2 tables ?

I know how to write the query to join them and find difference if Address1 is different, where I am struggling is with Address1 or Address2 or Address3 or City or State or Zip is different.
I am thinking this has to be a subquery of some sort, but am totally drawing a blank.

Any suggestions will be greatly appreciated.

jls

Is that going to be a fun query :) How about...

SELECT MIN(tname) as TNAME, ShipToNumber, Name, Address1, Address2, Address3, City, State, Zip
FROM
(SELECT 'TABLE1' as tname, table1.ShipToNumber, table1.Name, table1.Address1, table1.Address2, table1.Address3, table1.City, table1.State, table1.Zip FROM table1
UNION ALL
SELECT 'TABLE2' as tname, table2.ShipToNumber, table2.Name, table2.Address2, table2.Address2, table2.Address3, table2.City, table2.State, table2.Zip FROM table2
)TMPTBL GROUP BY ShipToNumber, Name, Address1, Address2, Address3, City, State, Zip HAVING COUNT(*)=1 ORDER BY ShipToNumber

Hope this helps

  What is the best way to write a query to find a mismatch between 2 tables?

  For instance, suppose you had 2 tables, each in a different database, but you wanted to make sure the addresses in each table were in sync.

  Table 1 = Ship To Number (PK), Name, Address1, Address2, Address3, City, State, Zip
  Table 2 = Ship To Number (PK), Name, Address1, Address2, Address3, City, State, Zip

  What is the syntax to find where a difference occurs between the 2 tables ?

  I know how to write the query to join them and find difference if Address1 is different, where I am struggling is with Address1 or Address2 or Address3 or City or State or Zip is different.
  I am thinking this has to be a subquery of some sort, but am totally drawing a blank.

  Any suggestions will be greatly appreciated.

  jls

You may find the CHECKSUM function useful for this task.

select *
from (select *, checksum(*) as cst1 from t1) as d1
  join (select *, checksum(*) as cst2 from t2) as d2
    on d1.keycol = d2.keycol
    and cst1 <> cst2

You will probably find most mismatches with this code. Just keep in mind that it's not 100% guaranteed that you will get all of them.
The CHECKSUM function might produce the same values for different inputs.

  What is the best way to write a query to find a mismatch between 2 tables?

  For instance, suppose you had 2 tables, each in a different database, but you wanted to make sure the addresses in each table were in sync.

  Table 1 = Ship To Number (PK), Name, Address1, Address2, Address3, City, State, Zip
  Table 2 = Ship To Number (PK), Name, Address1, Address2, Address3, City, State, Zip

  What is the syntax to find where a difference occurs between the 2 tables ?

  I know how to write the query to join them and find difference if Address1 is different, where I am struggling is with Address1 or Address2 or Address3 or City or State or Zip is different.
  I am thinking this has to be a subquery of some sort, but am totally drawing a blank.

  Any suggestions will be greatly appreciated.

  jls

can't return zero when no rows exist using aggregate function sum
Trigger not executing stored proc ...
UPDATE statement behavior with derived table
Table name as a variable
Alter column to Identity