comparing all the fields between two tables

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

SQL Server 2005 Forums

Transact-SQL (2005)

comparing all the fields between two tables

Author

Topic

itmasterw
Yak Posting Veteran

90 Posts

Posted - 2009-11-10 : 18:01:01

Hi,
I am going to be given two list of records which I am going to put in two different tables.
so one list I am going to put into table A and list 2 I am putting in table B. List 2 is the same data as list 1 except list 1 has Septembers data and list 2 has Octobers data; and both list has about 12 columns each.
What they want me to do is actually compare, for each record, each column of data and see if there is a change.
So if in record1 columnA = 21 and ColumnB = 43
and in record2 columnA = 21 and ColumnB = 22
this would be shown as a record that changed

The problem is that I do not know how you would write a select statement to compare column by column like this, can some one please help me.
Thank you

ITM

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-11-10 : 18:20:39

what is the main identifying field in these two tables? social security? bank account what? need that first. do each have same amount of rows? do you want to see if there is a difference on row level or column level. but till then try this


Select *, 
ColumnAChangeYesNo = 
Case 
   WHEN tbl1.columnA <> tbl2.columnA  THEN 'Yes'
   ELSE 'No'
END,
Case 
   WHEN tbl1.columnB <> tbl2.columnB  THEN 'Yes'
   ELSE 'No'

...etc
...etc
END
FROM tableA a
INNER JOIN tableB b
on a.ID = b.ID

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

itmasterw
Yak Posting Veteran

90 Posts

Posted - 2009-11-10 : 18:30:16

I know this is going to sound crazy but we do not think there are any keys that we can compare to. But in any case they want to compare column to column; so I think your approch here will work. Thank you And I like you saying here.

ITM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources