EXCEPT operator query to differentiate between new and changed rows

Question

I am using the EXCEPT Operator to compare data between two tables (as part of a final import I want to compare "today's data" with "yesterday's data" and only finally import new and changed rows.)

It (EXCEPT) seems to be exactly what I need, but does anyone know a way I could extend it so that in the resulting data subset I was able to differentiate between rows that are new and rows that are changed?

Answer 1

You could use the technique from this post.

--Demo setup
set nocount on
drop table if exists #T1
drop table if exists #T2
Create table #T1 (Id int, OtherData varchar(10))
Create table #T2 (Id int, OtherData varchar(10))

insert into #T1(Id,OtherData) values(1,'NewRow')
insert into #T1(Id,OtherData) values(2,'ChgRowB')

insert into #T2(Id,OtherData) values(2,'ChgRowA')

-----------------------------------------------
--The actual query
-----------------------------------------------

-- NEW
SELECT A.*, 'NEW'  AS 'CHANGE_TYPE'
FROM #T1  A
LEFT JOIN #T2  B ON B.ID = A.ID
WHERE B.ID IS NULL
UNION
-- MODIFIED
SELECT B.*, 'MODIFIED'  AS 'CHANGE_TYPE'
FROM (
        SELECT * FROM #T1 
        EXCEPT
        SELECT * FROM #T2
    ) S1
INNER JOIN #T2  B ON S1.ID = B.ID;

---

| Id | OtherData | CHANGE_TYPE |
|----|-----------|-------------|
| 1  | NewRow    | NEW         |
| 2  | ChgRowA   | MODIFIED    |