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

Question

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?

Answer 1

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.