lundi 2 février 2015

Comparing Column Value and Display the Differ Column


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
)


And Table Temp B,



CREATE TABLE B
(
ID INT,
Descr VARCHAR(100),
Qty INT
)


And here is my ddl query to fill the following tables.



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)


Now we have data on each table, which the ID is the primary 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 suggestion but still wont work.


using except



SELECT * FROM A
EXCEPT
SELECT * FROM B


not worked.


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 show me if difference value exist then it will show the differences column, else if no difference value found on certain column, then will show null value.


Would you guys reccomend me another solution / better query than me? Any hint or helps will be very appreciated.





Aucun commentaire:

Enregistrer un commentaire