如何检索从 UPDATE 语句的 OUTPUT 插入的行的标识值？

我相信这是因为@@IDENTITY与语句SCOPE_IDENTITY()结合使用时不起作用。UPDATE虽然我知道对此的自然反应是“为什么它不返回外部 INSERT 的 ID？” 答案是：因为 Scope。

UPDATE语句和INSERT语句（使用 的结果）OUTPUT作为一个语句执行，并且本质上在彼此的同一范围内。当引擎执行查询时，它会意识到这一点，因此无法跟踪外部生成的身份INSERT。您可以通过运行以下查询来证明这一点，该查询将 theUPDATE和 the拆分INSERT为两个单独的语句：

DECLARE @UpdateTable table 
(
    UpdateTableID int IDENTITY, 
    UpdateTableValue int
);
DECLARE @InsertTable table 
(
    InsertTableID int IDENTITY, 
    UpdateTableValue1 int, 
    UpdateTableValue2 int
);

DECLARE @TestValue int = 5;

INSERT INTO @UpdateTable (UpdateTableValue) 
VALUES (1),(2),(3);

SELECT [@@IDENTITY] = @@IDENTITY, [SCOPE_IDENTITY()] = SCOPE_IDENTITY();

DECLARE @tmp TABLE 
(
    UpdateTableValue1 int, 
    UpdateTableValue2 int
);

UPDATE      @UpdateTable
SET         UpdateTableValue = UpdateTableValue + @TestValue
OUTPUT      deleted.UpdateTableValue, inserted.UpdateTableValue
INTO        @tmp
WHERE       UpdateTableID = 2;

INSERT INTO @InsertTable (UpdateTableValue1, UpdateTableValue2)
SELECT
    UpdateTableValue1, UpdateTableValue2
FROM        @tmp;

SELECT [@@IDENTITY] = @@IDENTITY, [SCOPE_IDENTITY()] = SCOPE_IDENTITY();

这会产生正确的结果：

如果您需要从表格中获取身份，据我所知，您有 3 个选项：

1. 使用我上面使用的技术来获取值。
2. 在 final 中添加一个OUTPUT子句以INSERT捕获生成的 ID。
3. 使用该IDENT_CURRENT()函数获取当前值。

如果您使用的是临时表（不是全局临时表），那么IDENT_CURRENT()就可以了，因为您可以保证它只会返回您的会话中生成的值（因为这些对象不能通过其他会话获得）。但是，如果您给出的示例就是这样，那么我将使用选项 2 并使用另一个OUTPUT子句来捕获生成的标识值：

INSERT INTO @InsertTable (UpdateTableValue1, UpdateTableValue2)
OUTPUT inserted.InsertTableID
SELECT
    UpdateTableValue1, UpdateTableValue2
FROM        
(
    UPDATE      @UpdateTable
    SET         UpdateTableValue = UpdateTableValue + @TestValue
    OUTPUT      deleted.UpdateTableValue, inserted.UpdateTableValue
    WHERE       UpdateTableID = 2
) AS UpdateResults (UpdateTableValue1, UpdateTableValue2);

然后输出正确的值：