如何檢索從 UPDATE 語句的 OUTPUT 插入的行的標識值？

當從語句OUTPUT中插入該行時，如何檢索插入行的標識值？UPDATE既不@@IDENTITY也不SCOPE_IDENTITY()似乎設置正確。

考慮這段程式碼：

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

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

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

最後插入的行的標識值為 1，但@@IDENTITYand函式從執行最後一條語句之前SCOPE_IDENTITY()的原始值返回其原始值。INSERT

@@VERSION:

Microsoft SQL Azure (RTM) - 12.0.2000.8 2019 年 5 月 2 日 20:11:13 版權所有 (C) 2019 Microsoft Corporation

我相信這是因為@@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);

然後輸出正確的值：

引用自：https://dba.stackexchange.com/questions/238830