Como você recupera o valor de identidade de uma linha inserida na OUTPUT de uma instrução UPDATE?

Eu acredito que isso é porque @@IDENTITYe SCOPE_IDENTITY()não funciona quando usado em combinação com uma UPDATEdeclaração. Embora eu entenda que a resposta natural a isso seria "Bem, por que não retorna o ID do INSERT externo?" a resposta é: por causa do Escopo.

A UPDATEinstrução e a INSERTinstrução (usando os resultados do OUTPUT) são executadas como uma instrução e estão inerentemente dentro do mesmo escopo uma da outra. Quando o mecanismo executa a consulta, ele percebe isso e, portanto, não consegue rastrear a identidade gerada pelo arquivo INSERT. Você pode provar isso executando a seguinte consulta que divide the UPDATEe the INSERTem duas instruções individuais:

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

O que produz os resultados corretos:

Se você precisar obter a identidade da tabela, você tem 3 opções até onde eu vejo:

1. Use a técnica que eu uso acima para obter o valor.
2. Adicione uma OUTPUTcláusula ao final INSERTpara capturar o ID gerado.
3. Use a IDENT_CURRENT()função para obter o valor atual.

Se você estiver usando tabelas temporárias (não tabelas temporárias globais), IDENT_CURRENT()tudo bem, pois você pode garantir que ele retornará apenas o valor gerado dentro de sua sessão (já que os objetos não estão disponíveis por meio de outras sessões). No entanto, se o exemplo que você deu é apenas isso, eu usaria a opção 2 e usaria outra OUTPUTcláusula para capturar o valor de identidade gerado:

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

Isso então gera o valor correto: