sql-server - 在不使用输出的情况下通过 MERGE 的操作获取受影响行的行数

Question

我试图在合并语句之后通过操作（更新、插入、删除）获取受影响行的计数。由于我需要的唯一信息是计数，因此我宁愿避免将数据输出到单独的时间来处理时间问题。

我可以执行单独的插入、更新和删除语句，并从@@ROWCOUNT 获取我需要的信息。通过合并，我可以将数据输出到另一个表中，然后以结果为中心，但我希望像@@ROWCOUNT 这样的显式操作更灵活一些。

当前解决方案：

--Mock up a target table and a source table and populate with some data
DROP TABLE IF EXISTS TargetTable
CREATE TABLE TargetTable
  (
    Field1 int,
    Field2 int,
    Field3 int,
    Field4 int
  )

DROP TABLE IF EXISTS SourceTable
CREATE TABLE SourceTable
  (
    Field1 int,
    Field2 int,
    Field3 int,
    Field4 int
  )

INSERT INTO SourceTable 
  (
    Field1,
    Field2,
    Field3,
    Field4
  )
  VALUES ( 1, 2, 3, 7 ),
         ( 1, 2, 4, 7 ), 
         ( 1, 2, 5, 7 ),
         ( 1, 2, 6, 7 ),
         ( 2, 1, 1, 7 ),
         ( 2, 1, 2, 7 )

--Make a table to hold output
DROP TABLE IF EXISTS #Output
CREATE TABLE #Output
  (
    [Action] nvarchar(10)
  )

--Write a merge with the actions outputed into our temp table
MERGE TargetTable AS Target 
  USING SourceTable AS Source
    ON ( Target.Field1 = Source.Field1 
         AND Target.Field2 = Source.Field2
         AND Target.Field3 = Source.Field3 )
  WHEN NOT MATCHED BY Target
    THEN INSERT ( Field1,
                  Field2,
                  Field3,
                  Field4 )
         VALUES ( Source.Field1,
                  Source.Field2,
                  Source.Field3,
                  Source.Field4 )
  WHEN MATCHED 
    THEN UPDATE SET Target.Field4 = Source.Field4
  WHEN NOT MATCHED BY Source
    THEN DELETE
  OUTPUT $Action INTO #Output;

  --Pivot the results to get output by action
  SELECT [INSERT],
         [UPDATE],
         [DELETE]
    FROM #Output
    PIVOT ( COUNT( [Action] ) FOR [Action] IN ( [INSERT],
                                                [UPDATE],
                                                [DELETE] ) ) AS PivotedOutput

标签： sql-servertsqlmerge