How To Find a Mismatch between tables

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 yo
u wanted to make sure the addresses in each table were in sync.
Table 1 = Ship To Number (PK), Name, Address1, Address2, Address3, City, Sta
te, Zip
Table 2 = Ship To Number (PK), Name, Address1, Address2, Address3, City, Sta
te, 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 i
s 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.
jlsIs 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 FR
OM table1
UNION ALL
SELECT 'TABLE2' as tname, table2.ShipToNumber, table2.Name, table2.Address2,
table2.Address2, table2.Address3, table2.City, table2.State, table2.Zip FRO
M table2
)TMPTBL GROUP BY ShipToNumber, Name, Address1, Address2, Address3, City, Sta
te, Zip HAVING COUNT(*)=1 ORDER BY ShipToNumber
Hope this helps
--
Cheers,
JP (Just a programmer;)
----
A program is a device used to convert,
data into error messages
----
"JLS" <jlshoop@.hotmail.com> wrote in message news:e8wAsEZqFHA.3920@.TK2MSFTNG
P09.phx.gbl...
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 yo
u wanted to make sure the addresses in each table were in sync.
Table 1 = Ship To Number (PK), Name, Address1, Address2, Address3, City, Sta
te, Zip
Table 2 = Ship To Number (PK), Name, Address1, Address2, Address3, City, Sta
te, 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 i
s 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 tha
t it's not 100% guaranteed that you will get all of them.
The CHECKSUM function might produce the same values for different inputs.
--
BG, SQL Server MVP
www.SolidQualityLearning.com
"JLS" <jlshoop@.hotmail.com> wrote in message news:e8wAsEZqFHA.3920@.TK2MSFTNG
P09.phx.gbl...
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 yo
u wanted to make sure the addresses in each table were in sync.
Table 1 = Ship To Number (PK), Name, Address1, Address2, Address3, City, Sta
te, Zip
Table 2 = Ship To Number (PK), Name, Address1, Address2, Address3, City, Sta
te, 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 i
s 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