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

t-sql

Solution

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.

Problem

Say I have a table with an identity field. I want to insert a record in it if it doesn't already exist. In the below example, I check if the value stored in @Field1 already exists in the table. If not, I insert a new record: Definition of the table: ``` MyTable (MyTableId int Identity not null, Field1 int not null, Field2 int not null) ``` This is how I check if the value already exists and insert it if necessary ``` merge MyTable as t using (@Field1, @Field2) as s (Field1,Field2) on (t.Field1=s.Field1) when not matched then insert (Field1,Field2) values (s.Field1,s.Field2); ``` Getting the identity value when the record didn't already exist in the table can be done by adding: ``` output Inserted.MyTableId ``` but what if the record was already in the table (ie if there was a match)? The only way I found is to query the table after executing the Merge statement: ``` select MyTableId from MyTable where Field1=@Field1 ``` Is there a way to get the identity value directly from the Merge?

Original source