Merge into, When not matched by source and target in SQL

Question

I have 3 tables: Source, Target, Temp

My requirement is to perform a merge into based on primary key constraints in such a way that

1. When the source = target, UPDATE target with values of source
2. When it is not matched by target, meaning records from source are in target, I have insert the records from source to target
3. After all this, when the target record still remains unchanged or not updated(when not matched by source), then I have to write those records to the Temp table and delete them from target.

I am stuck at writing the records to Temp table and deleting the same records from target table.

    MERGE INTO dbo.Target_TBL AS Target USING dbo.Source_TBL AS Source
ON Target.SourceCode = Source.SourceCode
   AND Target.Date = Source.Date
   AND Target.Code = Source.Code
   AND Target.Number = Source.Number
   AND Target.Country = Source.Country
   AND Target.CategoryCode = Source.CategoryCode
   AND Target.TypeCode = Source.TypeCode

WHEN MATCHED THEN
   UPDATE
   SET Target.Qty = Source.Qty,
       Target.RecordUpdateTimestamp = Source.RecordUpdateTimestamp

WHEN NOT MATCHED BY TARGET THEN
    
   INSERT (SourceCode, Date, Code, Number, Country, CategoryCode, Qty, RecordCreationTimestamp, RecordUpdateTimestamp, TypeCode)
      VALUES (Source.SourceCode, Source.Date, Source.Code,
    Source.Number, Source.Country, Source.CategoryCode,
    Source.Qty, Source.RecordCreationTimestamp, Source.RecordUpdateTimestamp, Source.TypeCode)
    
    
WHEN NOT MATCHED BY SOURCE THEN

I tried 'insert into Temp table' but it was not allowed under 'WHEN NOT MATCHED BY SOURCE'. Please let me know how I can write to Temp table and also delete the records from the target table when not matched by source.

Answer 1

As I mention in the comments you're thinking about this the wrong way; you don't want to INSERT the data you want to DELETE into a temporary table and then DELETE the rows. Instead you should DELETE the rows and INSERT the details of those rows into a temporary table. You can achieve this by using an OUTPUT clause.

Firstly, some sample data:

CREATE TABLE dbo.DestinationTable (ID int, SomeValue varchar(3));

CREATE TABLE dbo.SourceTable (ID int, SomeValue varchar(3))
GO

INSERT INTO dbo.DestinationTable (ID,
                              SomeValue)
VALUES (1,'abc'),
       (2,'def');

INSERT INTO dbo.SourceTable (ID,
                             SomeValue)
VALUES(1,'bcd'),
      (3,'xyz');
GO

Personally I would suggest against a MERGE (I've already linked to a bunch of articles why), but I'll show the solution here anyway. As a MERGE can do INSERT, UPDATE and DELETE operations you'll need to capture what that action was, using $action, and then DELETE the rows in your temporary table you don't need. This gives you the following solution:

CREATE TABLE #DeletedData (Action nvarchar(10), ID int, SomeValue varchar(3));

GO

MERGE INTO dbo.DestinationTable WITH (HOLDLOCK) AS DT
USING dbo.SourceTable ST ON DT.ID = ST.ID
WHEN MATCHED THEN
    UPDATE
    SET SomeValue = ST.SomeValue
WHEN NOT MATCHED BY TARGET THEN
    INSERT (ID,SomeValue)
    VALUES(ST.ID,ST.SomeValue)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE
OUTPUT $action,
       Deleted.ID,
       Deleted.SomeValue
INTO #DeletedData;
GO
DELETE FROM #DeletedData
WHERE Action != 'DELETE';
GO
SELECT *
FROM dbo.DestinationTable;
GO
SELECT *
FROM #DeletedData;

Personally, however, and as mentioned, I would use an Upsert. This also makes capturing only the deleted rows easier. This would look like the following:

SET XACT_ABORT ON;
BEGIN TRANSACTION;

CREATE TABLE #DeletedData (ID int, SomeValue varchar(3));

UPDATE DT WITH (UPDLOCK, SERIALIZABLE)
SET SomeValue = ST.SomeValue
FROM dbo.DestinationTable DT
     JOIN dbo.SourceTable ST ON  DT.ID = ST.ID;

INSERT INTO dbo.DestinationTable (ID,
                                  SomeValue)
SELECT ST.ID,
       ST.SomeValue
FROM dbo.SourceTable ST
WHERE NOT EXISTS (SELECT 1
                  FROM dbo.DestinationTable DT
                  WHERE DT.ID = ST.ID);

DELETE DT
OUTPUT Deleted.ID,
       Deleted.SomeValue
INTO #DeletedData
FROM dbo.DestinationTable DT
WHERE NOT EXISTS (SELECT 1
                  FROM dbo.SourceTable ST
                  WHERE ST.ID = DT.ID);


COMMIT;
GO

db<>fiddle