CODEWITHSUNDEEP
sqlsql-server-2008t-sql
Master Yoda
Master Yoda

Reputation: 4412

TSQL - Use Except Clause to return column differences between tables

Im using a very simple script to return the differences between two tables, 'TableA' and a duplicate copy of TableA named 'CopyOfTableA':

SELECT TableId, Column1, Column2 FROM CopyOfTableA
EXCEPT
SELECT TableId, Column1, Column2 FROM TableA

This will subtract the differences between the CopyOfTableA table and TableA and return only those rows that contain differences.

This works fine and returns the rows that contain differences but my requirement is to return the rows that are different but only populate the columns that contain the differences so that I can see immediately where the differences are between rows.

Upvotes: 0

Views: 1558

Answers (1)

Jayvee
Jayvee

Reputation: 10875

Something on these lines:

select case when isnull(a.TableId,0)<>isnull(b.TableId,0) then 'Difference' else 'OK' end as TableIdCompare,
    case when isnull(a.column1,'')<>isnull(b.Column1,'') then 'Difference...' else 'OK' as Column1Compare,
    case when isnull(a.column2,'')<>isnull(b.Column2,'') then 'Difference...' else 'OK' as Column2Compare,
from CopyOfTableA a
full join TableA b

Please notice that when a row is missing it will show all the columns as differences. Also, you can tailor the case statements to be a bit more informational by adding the values on both tables, etc.

Upvotes: 2

Related Questions