Comparing 2 tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Comparing 2 tables

Author

Topic

DaveBF
Yak Posting Veteran

89 Posts

Posted - 2010-10-05 : 08:24:39

Hello,

I have 2 tables of information about people, and I would like to find whether certain columns have changed. For example, a table containing:

SSN (primary key)
Name
Address
Phone

My 2 tables are called Aug and Sep. I found the script below but cannot figure out how to use it. For example, I don't know what tbl_name is. Can you tell me how I can find the changed Name, Address, and Phone, or how to use the script below? By the way I am able to find new or missing SSN's. My problem is just with changed data in the other 3 columns, for which SSNs are in both tables. Thanks.

SELECT MIN (tbl_name) AS tbl_name, SSN, column_list
FROM
(
SELECT ' source_table ' as tbl_name, S.SSN, S.column_list
FROM source_table AS S
UNION ALL
SELECT 'destination_table' as tbl_name, D.SSN, D.column_list
FROM destination_table AS D
) AS alias_table
GROUP BY PK, column_list
HAVING COUNT (*) = 1
ORDER BY PK

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-05 : 08:34:50

What I understand by your post is that you want the records that were updated (or not tallying between aug and sep)

--No testing is done on script
--The below query will return you the records where either name or address or phone is mismatching between two tables. Aug table data columns will have prefix of Old and Sep table will have Current.

Try this:
Select A.SSN, A.Name as OldName, S.Name as CurrentName, A.Address as OldAddress,
S.Address as CurrentAddress, A.Phone as OldPhone , S.Phone as CurrentPhone
from
Aug A inner join Sep S
on A.SSN = S.SSN and
(A.Name <> S.Name or A.Address <> S.Address or A.Phone <> S.Phone)

DaveBF
Yak Posting Veteran

89 Posts

Posted - 2010-10-05 : 08:42:59

That works perfectly. Thank you, pk_bohra

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-05 : 09:05:32

You are welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources