0

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?

  • To detect "changes" you need a key. With equal key and different values on other columns, it's a update. With no matching key it's a new record. In any case, you can't use EXCEPT with all columns if you want to differentiate these 2 operations. – EzLo Jul 19 '18 at 13:44
  • Thanks. All I am doing is select * from newdata except select * from olddata and it seems to be doing what I want it to - i.e. isolate new records which weren't in olddata and also those rows which were in olddata but have changed values in newdata. – Seamus Jul 19 '18 at 13:57
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    |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.