Using MERGE to delete data, or insert

Question

From the UI I pass a datatable to a stored procedure. The type of that parameter is a user defined table field with the following structure

Personkey int
ComponentKey varchar

This data needs to go into a table, and data that exists in the table but is not in the datatable should be deleted.

Example table data

PersonKey     ComponentKey
123           A1
456           B9

And my datatable has 2 rows, one matching row and one new row

Example datatable data

PersonKey    ComponentKey
123          A1
786          Z6

The result is that the 456/B9 row should be deleted, nothing should happen to the 123/A1 row, and the 786/Z6 row should be inserted.

I believe I can use the MERGE statement but I am not sure how to form it.

I understand that WHEN NOT MATCHED I should do the insert but where does the delete part come into it?

MERGE Components
USING @passedInData
ON PersonKey = DatatblPersonKey AND ComponentKey = DatatblComponentKey

WHEN MATCHED THEN
-- DO nothing...

WHEN NOT MATCHED
INSERT (PersonKey, ComponentKey) VALUES (DatatblPersonKey, DatatblComponentey);

Edit: Just to be clear, the datatable could contain many rows for the same person key, but the component key would be different.

Example datatable data

PersonKey    ComponentKey
123          Z6
123          C5

Example table data

PersonKey     ComponentKey
123           A1
456           B9

The result after inserting the above datatable should be

PersonKey    ComponentKey
123          Z6
123          C5
456          B9

Notice that 123/A1 has been deleted and 456/B9 is still in the table.

btberry · Answer 1 · 2016-08-31T23:25:27.110

The default "WHEN NOT MATCHED" assumes that what you really mean is "WHEN NOT MATCHED BY TARGET". You can do another statement for "WHEN NOT MATCHED BY SOURCE" with the simple command "DELETE".

Be careful when you do this because it will delete all the records from the target that don't match the source based on the comparison you have specified. If it's necessary to do a subset of the target for that action, you can use a cte with that filter and then do your merge against that cte as the target.

edit ... demonstrating how to hook up what I am saying:

DECLARE @databaseTable TABLE (PersonKey INT, ComponentKey VARCHAR(10));
INSERT INTO @databaseTable
VALUES
    (123, 'A1'),
    (456, 'B9');
DECLARE @appDataset TABLE (PersonKey INT, ComponentKey VARCHAR(10));
INSERT INTO @appDataset
VALUES
    (123, 'Z6'),
    (123, 'C5');

WITH cteTarget AS
    (
    SELECT  dt.PersonKey
        ,   dt.ComponentKey
    FROM @databaseTable AS dt
    JOIN (SELECT DISTINCT PersonKey FROM @appDataset) AS pk
        ON pk.PersonKey = dt.PersonKey
    )
MERGE cteTarget AS tgt
USING @appDataset AS src
    ON src.PersonKey = tgt.PersonKey
    AND src.ComponentKey = tgt.ComponentKey
WHEN NOT MATCHED BY SOURCE THEN
    DELETE
WHEN NOT MATCHED BY TARGET THEN
    INSERT
        (PersonKey
        ,ComponentKey)
    VALUES
        (src.PersonKey
        ,src.ComponentKey);

SELECT * FROM @databaseTable;

Your execution may not have worked, but the approach does. I edited my answer to include constructs of the data and the coded solution for your problem. Again, keep in my that you have to take care to define what exactly your target is. — btberry, Aug 31 '16 at 23:27
Worth mentioning that MERGE cannot match columns where the value is NULL, so if src.ComponentKey and tgt.ComponentKey are both NULL, although that is actually a match, SQL will not treat it like one. The way to avoid this is to make sure you ISNULL every column in your USING statement. — Geoff Griswald, Jan 29 '21 at 12:03

Using MERGE to delete data, or insert

1 Answers1