确定SQL MERGE语句的结果 [英] Determining SQL MERGE statement result

问题描述

调用MERGE之后，是否可以确定记录是否匹配(记录是插入还是更新)?

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

理想情况下，我想将其输出到参数.

Ideally I'd like to output it to a parameter.

我使用以下语句输出合并语句，该语句输出在管理工作室中发生的情况: 假设我有以下合并语句:

I've got the merge statement outputting what happened in my management studio using the following statement: Say I had the following merge statement:

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;

我正在尝试使用参数来获取"$ action"输出.

I'm trying to use a parameter to get the '$action' output.

推荐答案

您可以做的是创建一个临时表(或表变量)并将其发送到那里-在OUTPUT子句中添加一些有意义的字段以使其清楚在哪排 受什么动作影响:

What you could do is create a temporary table (or a table variable) and send your output there - add some meaningful fields to your OUTPUT clause to make it clear what row was affected by what 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

更新:嗯，好的，所以您想从.NET调用它！好吧，在这种情况下，只需在SqlCommand对象上使用.ExecuteReader()方法调用它-您使用OUTPUT...输出的内容将作为结果集返回给.NET调用者-您可以循环遍历该对象:

UPDATE: ah, okay, so you want to call this from .NET ! Well, in that case, just call it using the .ExecuteReader() method on your SqlCommand object - the stuff you're outputting using OUTPUT... will be returned to the .NET caller as a result set - you can loop through that:

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();
}

您应该从该数据读取器中获得结果"$ action".

You should get back the resulting "$action" from that data reader.

这篇关于确定SQL MERGE语句的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！