Compare 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

Compare 2 tables

Author

Topic

byka
Starting Member

18 Posts

Posted - 2014-08-14 : 09:22:55

How would I compare 2 rows to find the difference in the columns?
Example:
ID Column1 Column2 Column3
1 Text1 Text4
2 Text1 Text2 Text3

Result:
Column1: Text2 New
Column3: Text4 Old Text3 New

byka

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-14 : 11:48:14

Here's one way:


declare @t table (id int, column1 char(5), column2 char(5), column3 char(5))
insert into @t(id, column1, column2, column3) values
	(1, 'Text1', 'Text4', null),
	(2, 'Text1', 'Text2', 'Text3')

; with src as (
select case when isnull(t1.column1, '') <> isnull(t2.column1, '')
			then 'Column1 :' + isnull(t1.column1, '') + ' Old ' + isnull(t2.column1, '') + ' New' end col1txt
	 , case when isnull(t1.column2, '') <> isnull(t2.column2, '')
			then 'Column2 :' + isnull(t1.column2, '') + ' Old ' + isnull(t2.column2, '')  + ' New' end col2txt
	 , case when isnull(t1.column3, '') <> isnull(t2.column3, '')
			then 'Column3 :' + isnull(t1.column3, '') + ' Old ' + isnull(t2.column3, '')  + ' New' end col3txt
from @t t1

cross join @t t2

where t2.id = t1.id+1
  and (
	  isnull(t1.column1, '') <> isnull(t2.column1, '')
   or isnull(t1.column2, '') <> isnull(t2.column2, '')
   or isnull(t1.column3, '') <> isnull(t2.column3, '')
   )
)

select pvt.txt from src
unpivot (txt for col in (col1txt, col2txt, col3txt)) pvt

byka
Starting Member

18 Posts

Posted - 2014-08-14 : 13:47:03

My Id can be different...
this line will not work...
where t2.id = t1.id+1

Please help me with adjustments..

byka

byka
Starting Member

18 Posts

Posted - 2014-08-14 : 13:59:17

I think I figure out:
ROW_NUMBER() OVER(ORDER BY PlanID DESC)

I have last question. is it possible to separate Old and new values into 2 different columns?

byka

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-14 : 14:09:38

Yes, use ROW_NUMBER()

Actually without the pivot, the results ARE in separate columns,

byka
Starting Member

18 Posts

Posted - 2014-08-14 : 14:12:39

I have uncommented
SELECT pvt.txt
FROM src --UNPIVOT (txt for col in (col1txt, col2txt, col3txt)) pvt

But it didn't work

byka

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-14 : 14:17:06

Of course that didn't work! You told it to SELECT pvt.txt but now there is no pvt.txt!

Try just SELECT * from src in the same place