How do you get the identity value after using MERGE when there is a match?

Question

In the case when the record already exists, you can store the matched id into a variable like this:

DECLARE @MatchedId INTEGER;

MERGE MyTable as t
....
....
WHEN MATCHED THEN
    UPDATE SET @MatchedId = t.MyTableId;

UPDATE:
Here’s a full example. This demonstrates one way:

DECLARE @UpdateVariable bit
DECLARE @ChangeResult TABLE (ChangeType VARCHAR(10), Id INTEGER)
DECLARE @Data TABLE (Id integer IDENTITY(1,1), Val VARCHAR(10))
INSERT @Data ([Val]) VALUES ('A');

MERGE @data AS TARGET
USING (SELECT 'A' AS Val UNION ALL SELECT 'B' AS Val) AS SOURCE ON TARGET.Val = SOURCE.Val
WHEN NOT MATCHED THEN
    INSERT ([Val])
    VALUES (SOURCE.Val)
WHEN MATCHED THEN 
    UPDATE SET @UpdateVariable = 1
OUTPUT $action, inserted.Id INTO @ChangeResult;

SELECT * FROM @data
SELECT * FROM @ChangeResult

Points to note are:

$action will give you what type of action was performed for a row (INSERT, UPDATE, DELETE)
@ChangeResult table will hold the info as to what types of changes were made
for the WHEN MATCHED case, I am basically setting a dummy variable. This doesn’t serve any purpose here other than to ensure the UPDATE path gets hit to generate the UPDATE row in the output. i.e. that @UpdateVariable is not used for anything else. If you actually wanted to update the existing row, then you’d put a proper UPDATE in here, but in the case where you don’t want to actually UPDATE the existing row, then this “dummy” update seems to be required.

Leave a Comment Cancel reply