Determining the SQL MERGE Result

Question

Is there a way to determine if a record was mapped or not (whether a record was inserted or updated) after calling MERGE?

Ideally, I would like to output it to a parameter.

Edit: I have a merge operator outputting what happened in my management studio using the following operator: Let's say I had the following merge expression:

MERGE INTO TestTable as target USING ( select '00D81CB4EA0842EF9E158BB8FEC48A1E' ) AS source (Guid) ON ( target.Guid = source.Guid ) WHEN MATCHED THEN UPDATE SET Test_Column = NULL WHEN NOT MATCHED THEN INSERT (Guid, Test_Column) VALUES ('00D81CB4EA0842EF9E158BB8FEC48A1E', NULL) OUTPUT $action; 

I am trying to use a parameter to get the output of $ action.

Answer 1

What you can do is create a temporary table (or a table variable) and send your output there - add several meaningful fields to your OUTPUT clause so that it is clear which row had which action:

 DECLARE @OutputTable TABLE (Guid UNIQUEIDENTIFIER, Action VARCHAR(100)) MERGE INTO TestTable as target USING ( select '00D81CB4EA0842EF9E158BB8FEC48A1E' ) AS source (Guid) ON ( target.Guid = source.Guid ) WHEN MATCHED THEN UPDATE SET Test_Column = NULL WHEN NOT MATCHED THEN INSERT (Guid, Test_Column) VALUES ('00D81CB4EA0842EF9E158BB8FEC48A1E', NULL) OUTPUT INSERTED.Guid, $action INTO @OutputTable SELECT Guid, Action FROM @OutputTable 

UPDATE: ah, good, so you want to call it from .NET! In this case, just call it using the .ExecuteReader() method on your SqlCommand object - the material that you output using OUTPUT... will be returned to the .NET caller as a result set - you can skip this

 using(SqlCommand cmd = new SqlCommand(mergeStmt, connection)) { connection.Open(); using(SqlDataReader rdr = cmd.ExecuteReader()) { while(rdr.Read()) { var outputAction = rdr.GetValue(0); } rdr.Close(); } connection.Close(); } 

You must return the result of "$ action" from this data reader.