Show only fields from table1 different than table2 (Sql server 2008)

Question

I get a file every day that may or may not have changes from the previous day, I copy the file I receive and call it table2, the next day I receive the file I load it in table1 but now I want to see what's changed. The problem is, I don't want to see the entire row, I just want to see what has changed.

CREATE TABLE Table1
([Emp_ID] int, [DOB] date, [code] varchar(10))
;

INSERT INTO Table1
 ([Emp_ID], [DOB], [code])
VALUES
 (55556, '1966-01-15', '5454'),
 (55557, '1980-03-21', '6868'),
 (55558, '1985-04-26', '7979'),
 (55559, '1990-10-17', '1212'),
 (55560, '1992-12-30', '6767')
;
CREATE TABLE Table2
([Emp_ID] int, [DOB] date, [code] varchar(10))
;

INSERT INTO Table2
 ([Emp_ID], [DOB], [code])
VALUES
 (55556, '1966-01-15', '5454'),
 (55557, '1980-03-21', '6868'),
 (55558, '1985-04-26', '7979'),
 (55559, '1990-10-17', '1212'),
 (55560, '1992-12-30', '5555')
;

Now if I use the EXCEPT function I will see

select * from table1
except
select * from table2

EMP_ID  DOB         CODE
55560   1992-12-30  6767

But what If I just want to see the EMP_ID and the field that has changed, so I want to see this result.

EMP_ID  DOB         CODE
55560               6767

Because the Code is all that has changed for that EMP_ID?

Thanks in advance for any suggestions!

Answer 1

You can use a Common Table Expression (CTE) to determine the differences. Then join those results with the initial table, using a CASE expression on each field to determine if the particular field has changed.

;with UpdatedRecords as
( select * from table1
  except
  select * from table2
)
select ur.EMP_ID, 
       CASE WHEN ur.DOB <> t2.DOB THEN ur.DOB ELSE NULL END as DOB,
       CASE WHEN ur.code <> t2.code THEN ur.code ELSE NULL END as code

from UpdatedRecords ur
join table2 t2 on ur.EMP_ID=t2.EMP_ID