compare table columns, urgent please help!!!

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)

compare table columns, urgent please help!!!

Author

Topic

sqlserverdeveloper
Posting Yak Master

243 Posts

Posted - 2009-03-24 : 17:34:03

I have two tables, table1 and table2 with same data structure and columns. Table1 has old data and table2 has new data.
I need to compare each column of table1 and table2 and display only the changed data columns, for example, if col1 and col2 have the differences, then we need to display the final output as follows:

select table1.col1, table2.col1, table1.col2, table2.col2
.....

Please let me know. Thanks.

revdnrdy
Posting Yak Master

220 Posts

Posted - 2009-03-24 : 18:40:19

Well you could use a left join and any rows in oldtable will appear as nulls.

select NewTable.col1, OldTable.col1
from NewTable
left join Oldtable on (NewTable.id = Oldtable.id)

something like that should give you all the matching records. All rows from NewTable would be listed since they are on the left side of the join. The records in old table would be displayed as null if they didn't map.

You could also run a straight compare between the rows of the tables and calculate deltas if thats more important.

r&r

sqlserverdeveloper
Posting Yak Master

243 Posts

Posted - 2009-03-24 : 23:11:52

Below is my requirement:
Table1:
empID LastName FirstName
1 Michael Tom
2 Wacko Mark
3 Brad Gates

Table2:
empID LastName FirstName
1 Michelle Tom
2 Yo-Yo Mark
3 Brad Ma

From the above tables when I pass empID as a parameter: I should get the following output, only the columns with different values need to be displayed:

output for for empId:1
empID LastName
1 Michael
1 Michelle

output for for empId:3
empID FirstName
3 Gates
3 Ma

Please let me know. Thanks.

chrianth
Yak Posting Veteran

50 Posts

Posted - 2009-03-25 : 02:01:18

Below will let you get those with changes on LastName. Do the same approach to get those with changes on FirstName column.

(
select NewTable.empid,NewTable.LastName
from NewTable
join OldTable
on NewTable.empid = OldTable.empid
where NewTable.LastName <> OldTable.LastName
UNION ALL
select OldTable.empid,OldTable.LastName
from NewTable
join OldTable
on NewTable.empid = OldTable.empid
where NewTable.LastName <> OldTable.LastName
) order by 1

Hope this helps.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources