MERGE "MySchema"."Point" AS t
USING (
       SELECT "ObjectId","PointName",z."Id" AS "LocationId",i."Id" AS "Region"
         FROM @p1 AS d
         JOIN "MySchema"."Region" AS i ON i."Name" = d."Region"
    LEFT JOIN "MySchema"."Location" AS z ON z."Name" = d."Location" AND z."Region" = i."Id"
       ) AS s
   ON s."ObjectId" = t."ObjectId"
 WHEN NOT MATCHED BY TARGET 
    THEN INSERT ("ObjectId","Name","LocationId","Region") VALUES (s."ObjectId",s."PointName",s."LocationId",s."Region")
 WHEN MATCHED 
    THEN UPDATE 
     SET "Name" = s."PointName","LocationId" = s."LocationId","Region" = s."Region"
OUTPUT $action,inserted.*,deleted.*;

但是,这会导致会话终止,并出现以下错误：

Msg 0,Level 11,State 0,Line 67 A severe error occurred on the
current command. The results,if any,should be discarded.

Msg 0,Level 20,Line 67 A severe error occurred on the current
command. The results,should be discarded.

我把一个简短的测试脚本放在一起产生错误：

USE master;
GO
IF DB_ID('TEST') IS NOT NULL
DROP DATABASE "TEST";
GO
CREATE DATABASE "TEST";
GO
USE "TEST";
GO

SET NOCOUNT ON;

IF SCHEMA_ID('MySchema') IS NULL
EXECUTE('CREATE SCHEMA "MySchema"');
GO

IF OBJECT_ID('MySchema.Region','U') IS NULL
CREATE TABLE "MySchema"."Region" (
"Id" TINYINT IDENTITY NOT NULL CONSTRAINT "PK_MySchema_Region" PRIMARY KEY,"Name" VARCHAR(8) NOT NULL CONSTRAINT "UK_MySchema_Region" UNIQUE
);
GO

INSERT [MySchema].[Region] ([Name]) 
VALUES (N'A'),(N'B'),(N'C'),(N'D'),(N'E'),( N'F'),(N'G');

IF OBJECT_ID('MySchema.Location','U') IS NULL
CREATE TABLE "MySchema"."Location" (
"Id" SMALLINT IDENTITY NOT NULL CONSTRAINT "PK_MySchema_Location" PRIMARY KEY,"Region" TINYINT NOT NULL CONSTRAINT "FK_MySchema_Location_Region" FOREIGN KEY REFERENCES "MySchema"."Region" ("Id"),"Name" VARCHAR(128) NOT NULL,CONSTRAINT "UK_MySchema_Location" UNIQUE ("Region","Name") 
);
GO

IF OBJECT_ID('MySchema.Point','U') IS NULL
CREATE TABLE "MySchema"."Point" (
"ObjectId" BIGINT NOT NULL CONSTRAINT "PK_MySchema_Point" PRIMARY KEY,"Name" VARCHAR(64) NOT NULL,"LocationId" SMALLINT NULL CONSTRAINT "FK_MySchema_Point_Location" FOREIGN KEY REFERENCES "MySchema"."Location"("Id"),"Region" TINYINT NOT NULL CONSTRAINT "FK_MySchema_Point_Region" FOREIGN KEY REFERENCES "MySchema"."Region" ("Id"),CONSTRAINT "UK_MySchema_Point" UNIQUE ("Name","Region","LocationId")
);
GO

-- CONTAINS HISTORIC Point DATA
IF OBJECT_ID('MySchema.PointHistory','U') IS NULL
CREATE TABLE "MySchema"."PointHistory" (
"Id" BIGINT IDENTITY NOT NULL CONSTRAINT "PK_MySchema_PointHistory" PRIMARY KEY,"ObjectId" BIGINT NOT NULL,"LocationId" SMALLINT NULL,"Region" TINYINT NOT NULL
);
GO

CREATE TYPE "MySchema"."PointTable" AS TABLE (
"ObjectId"      BIGINT          NOT NULL PRIMARY KEY,"PointName"     VARCHAR(64)     NOT NULL,"Location"      VARCHAR(16)     NULL,"Region"        VARCHAR(8)      NOT NULL,UNIQUE ("PointName","Location")
);
GO

DECLARE @p1 "MySchema"."PointTable";

insert into @p1 values(10001769996,N'ABCDEFGH',N'N/A',N'E')

MERGE "MySchema"."Point" AS t
USING (
       SELECT "ObjectId",deleted.*;

如果我删除OUTPUT子句,则不会发生错误.此外,如果我删除已删除的引用,则不会发生错误.所以我查看了MSTP文档中的OUTPUT子句,其中说明：

DELETED cannot be used with the OUTPUT clause in the INSERT statement.

这对我来说很有意义,但MERGE的全部意义在于你可能事先并不知道.

此外,无论采取什么操作,下面的脚本都可以正常工作：

USE tempdb;
GO
CREATE TABLE dbo.Target(EmployeeID int,EmployeeName varchar(10),CONSTRAINT Target_PK PRIMARY KEY(EmployeeID));
CREATE TABLE dbo.Source(EmployeeID int,CONSTRAINT Source_PK PRIMARY KEY(EmployeeID));
GO
INSERT dbo.Target(EmployeeID,EmployeeName) VALUES(100,'Mary');
INSERT dbo.Target(EmployeeID,EmployeeName) VALUES(101,'Sara');
INSERT dbo.Target(EmployeeID,EmployeeName) VALUES(102,'Stefano');

GO
INSERT dbo.Source(EmployeeID,EmployeeName) Values(103,'Bob');
INSERT dbo.Source(EmployeeID,EmployeeName) Values(104,'Steve');
GO
-- MERGE statement with the join conditions specified correctly.
USE tempdb;
GO
BEGIN TRAN;
MERGE Target AS T
USING Source AS S
ON (T.EmployeeID = S.EmployeeID) 
WHEN NOT MATCHED BY TARGET AND S.EmployeeName LIKE 'S%' 
    THEN INSERT(EmployeeID,EmployeeName) VALUES(S.EmployeeID,S.EmployeeName)
WHEN MATCHED 
    THEN UPDATE SET T.EmployeeName = S.EmployeeName
WHEN NOT MATCHED BY SOURCE AND T.EmployeeName LIKE 'S%'
    THEN DELETE 
OUTPUT $action,deleted.*;
ROLLBACK TRAN;
GO

此外,我还有其他查询使用OUTPUT的方式与抛出错误的方式相同,并且它们完美地工作 – 它们之间的唯一区别是参与MERGE的表.

这对我们来说是生产中的主要问题.我已经在VM2014和sql2016上在128和RAM,12 x 2.2GHZ内核,Windows Server 2012 R2的VM和物理上重现了这个错误.

可以在此处找到从查询生成的估计执行计划：

Estimated Execution Plan