Find Modified/New/Deleted Records Between Two Tables

Question

I want to find new, modified and deleted records in one table (tableA) by comparing it to another table (tableB). Both tables are of the same schema and has a unique ID field.

In my situation, tableA is originally the same as tableB but it has been edited by some external organisation and once they have done their edits, they send the table back via ZIP file, and we re-populate (truncate and insert) that data to tableA. So I want to find out what records have changed in tableA. I am using SQL Server 2012.

I can get new and modified records with the "except" keyword:

select * from tableA
except
select * form tableB

(Let's call the above results ResultsA)

I can also get deleted and modified records:

select * from tableB
except
select * form tableA

(Let's call the above results ResultsB)

The problem is, both ResultsA and ResultsB have the same records that have been modified/edited. So the modified/edited records are doubled up. I can use inner join or intersect on ResultsA and ResultsB to get just the modified records (call this results ResultsC). But then I will need to use join/except again between ResultsA and ResultsC to get just the new records, and join/except again between ResultsB and ResultsC to get just the deleted records... I tried this and this but they are not working for me.

Obviously this is not good. Are there any elegant and simpler ways to find out the records that have been deleted, modified or added in tableA compared to tableB?

BeaglesEnd · Accepted Answer · 2014-11-25T12:35:33.180

How about:

-- DELETED
SELECT B.*, 'DELETED' AS 'CHANGE_TYPE'
FROM TableB B
LEFT JOIN TableA A  ON B.PK_ID = A.PK_ID
WHERE A.PK_ID IS NULL
UNION
-- NEW
SELECT A.*, 'NEW'  AS 'CHANGE_TYPE'
FROM TableA  A
LEFT JOIN TableB  B ON B.PK_ID = A.PK_ID
WHERE B.PK_ID IS NULL
UNION
-- MODIFIED
SELECT B.*, 'MODIFIED'  AS 'CHANGE_TYPE'
FROM (
        SELECT * FROM TableA 
        EXCEPT
        SELECT * FROM TableB 
    ) S1
INNER JOIN TableB  B ON S1.PK_ID = B.PK_ID;

Not exactly elegant, but it works.

score 1 · Answer 2 · answered Nov 25 '14 at 12:57

Based on what i understood i came up with the following solution.

DECLARE @tableA TABLE (ID INT, Number INT)
DECLARE @tableB TABLE (ID INT, Number INT)

INSERT INTO @tableA VALUES
    (1,10),
    (2,20),
    (3,30),
    (4,40)

INSERT INTO @tableB VALUES
    (1,11),
    (2,20),

    (4,40),
    (5,50)


SELECT *,'Modified or deleted' as 'Status' FROM
(
    select * from @tableA
    except
    select * from @tableB
)a WHERE ID NOT IN 
(
    select ID from @tableB
    except
    select ID from @tableA
)
UNION
SELECT *,'New' as 'Status' FROM 
(
    select * from @tableB
    except
    select * from @tableA
)b WHERE ID NOT IN 
(
    SELECT ID FROM
    (
        select * from @tableA
        except
        select * from @tableB
    )a WHERE ID NOT IN 
    (
        select ID from @tableB
        except
        select ID from @tableA
    )
)

Thanks - that's what I had in mind but I thought it seemed inefficient as there are may 'except' statements, hence I asked the question here in SO. Up-voted it anyhow as it works and is in one-single statement. — henrykodev, Nov 26 '14 at 10:05

score 0 · Answer 3 · answered Nov 25 '14 at 10:21

You can use the OUTPUT clause:

Returns information from, or expressions based on, each row affected by an INSERT, UPDATE, or DELETE statement. These results can be returned to the processing application for use in such things as confirmation messages, archiving, and other such application requirements. Alternatively, results can be inserted into a table or table variable.

See the the following, sorry I don't have a practical code for you. But note the SQL output clause can be used to return any value from ‘inserted’ and ‘deleted’ (New value and Old value) tables when doing an insert or update. follow this for more info

Thanks for the answer... however unfortunately tableA is not edited (insert, update, delete) directly. With our workflow, data is edited with other organisations and they send the data back to us with ZIP files. We unzip the file and repopulate tableA, and compare to tableB... I have edited my question to clarify that. Apologies for not clarifying it initially. — henrykodev, Nov 25 '14 at 10:33

Chris Masiero · Answer 4 · 2017-12-13T07:50:24.267

Another solution that works quite efficiently is to use a where not exists an intersect between the two tables. Its very compact.

SELECT
    IsNull(tableB.ID,tableA.ID) as 'ID',
    IsNull(tableB.Number,tableA.Number) as 'Number',
    'Action' = CASE 
                   WHEN tableB.ID IS NULL THEN 'Deleted'
                   WHEN tableA.ID IS NULL THEN 'Created'
                   ELSE 'Updated'
               END
FROM tableA
FULL OUTER JOIN tableB 
    ON tableB.ID = tableA.ID
WHERE 
    NOT EXISTS (SELECT tableB.* INTERSECT SELECT tableA.*)

This keeps the table scans down to a minimum, and provides detection of new, deleted and changed records.

I put all three from here into fiddle, and its surprising how differently they all compile.

http://sqlfiddle.com/#!6/b1a5a/5

score 0 · Answer 5 · answered Sep 26 '17 at 04:49

declare @DBOrderItem table
(
    OrderItemGuid UniqueIdentifier default newid(),
    Name VarChar(100)
);

declare @PayloadOrderItem table
(
    OrderItemGuid UniqueIdentifier default newid(),
    Name VarChar(100)
);

insert into @DBOrderItem (Name) values ('Phone');
insert into @DBOrderItem (Name) values ('Laptop');

insert into @PayloadOrderItem 
    select top 1 * from @DBOrderItem;
insert into @PayloadOrderItem (Name) values ('Tablet');


select doi.OrderItemGuid, 
    doi.Name, 
    case when poi.OrderItemGuid is null then 'Delete' else 'Update' end ActionType
from @DBOrderItem doi
    left join @PayloadOrderItem poi on doi.OrderItemGuid = poi.OrderItemGuid
union
select poi.OrderItemGuid, 
    poi.Name, 
    'Add' ActionType
from @PayloadOrderItem poi
    left join @DBOrderItem doi on doi.OrderItemGuid = poi.OrderItemGuid
where doi.OrderItemGuid is null;

score 0 · Answer 6 · answered May 25 '18 at 09:57

This one works without primary key also a bit more elegant .(in my opinion!)

WITh A AS (SELECT 1,2,3 FROM DUAL
UNION ALL
SELECT 1,3,2 FROM DUAL
UNION ALL
SELECT 1,3,1 FROM DUAL),
B AS (SELECT 1,3,2 FROM DUAL
UNION ALL
SELECT 1,2,3 FROM DUAL
UNION ALL
SELECT 1,3,5 FROM DUAL
)
,
C AS 
(SELECT * FROM A
MINUS 
SELECT * FROM B
),
D AS( SELECT * FROM b
MINUS 
SELECT * FROM A)
SELECT C.* ,'Deleted' FROM  c
UNION  ALL
SELECT D.* ,'Added' FROM D

Find Modified/New/Deleted Records Between Two Tables

6 Answers6