This question already has an answer here:
Supposed I have table temp A and table temp B:
IF OBJECT_ID('tempdb..#A') IS NOT NULL BEGIN DROP TABLE #A END IF OBJECT_ID('tempdb..#B') IS NOT NULL BEGIN DROP TABLE #B END CREATE TABLE #A ( ID Int, Descr VARCHAR(100), Qty Int ) CREATE TABLE B ( ID INT, Descr VARCHAR(100), Qty INT ) INSERT INTO #A VALUES (1, 'Data 1' , 10) INSERT INTO #A VALUES (2, 'Data 2', 5) INSERT INTO #B VALUES (1, 'Data x' , 8) INSERT INTO #B VALUES (2, 'Data x' , 1)
The ID is a key in this case. The problem is, how we can fetch the column value differences and showing the column differ on our select query?
I have tried the following:
SELECT * FROM A EXCEPT SELECT * FROM B
using join and case.. when
select Case When #A.Descr = Before.Descr THen Null Else #A.Descr End, Case When #A.Qty = Before.Qty THen Null Else #A.Qty End from #A Left Join #B Before on #A.ID = Before.ID
This query shows me if different values exist then it will show the differences column, else if no difference value found on certain column, then will show null value.
Can you recommend a better solution?