Início / dba / Perguntas / 191146
Accepted
Louis Somers
Asked: 2017-11-18 03:57:22 +0800 CST

Usando colunas de origem na cláusula OUTPUT INTO de uma instrução INSERT (SQL Server)

  • 772

Estou escrevendo uma instrução de inserção de processamento em lote e gostaria de usar uma tabela temporária para acompanhar os IDs inseridos em vez de fazer um loop pelos itens e chamar SCOPE_IDENTITY() para cada linha inserida.

Os dados que precisam ser inseridos possuem ID's (temporários) ligando-os a outros dados que também devem ser inseridos em outra tabela, então preciso de uma referência cruzada do Id real e do Id temporário.

Este é um exemplo do que tenho até agora:

-- The existing table 
DECLARE @MyTable TABLE (ID INT IDENTITY(1,1), [Name] NVARCHAR(MAX));

-- My data I want to insert
DECLARE @MyInsertData TABLE (ID INT, [Name] NVARCHAR(MAX));
INSERT INTO @MyInsertData ( ID,Name)
VALUES ( -1 , 'bla'),(-2,'test'),(-3,'last');

DECLARE @MyCrossRef TABLE ([NewId] INT, OldId INT);

INSERT INTO @MyTable ( [Name] )
   OUTPUT Inserted.ID, INS.ID INTO @MyCrossRef
   SELECT [NAME] FROM @MyInsertData INS

-- Check the result
SELECT * FROM @MyCrossRef

O problema é que não consigo fazer com que a cláusula OUTPUT INTO aceite o ID, já tentei @MyInsertData.IDe outros truques juntando a tabela a ela mesma, mas nada parece funcionar.

insert output-clause

2 respostas

  1. Best Answer

    Na verdade, você pode conseguir a mesma coisa alterando seu INSERTpara um arquivo MERGE. Embora a MERGEinstrução seja realmente uma maneira bem legal de fazer "upserts" no SQL Server, não há nada que impeça você de usá-la apenas para inserir:

    -- The existing table 
DECLARE @MyTable TABLE (ID INT IDENTITY(1,1), [Name] NVARCHAR(MAX));

-- My data I want to insert
DECLARE @MyInsertData TABLE (ID INT, [Name] NVARCHAR(MAX));
INSERT INTO @MyInsertData ( ID,Name)
VALUES ( -1 , 'bla'),(-2,'test'),(-3,'last');

DECLARE @MyCrossRef TABLE ([NewId] INT, OldId INT);

MERGE INTO @MyTable AS dest
USING @MyInsertData AS ins ON 1=0   -- always false

WHEN NOT MATCHED BY TARGET          -- happens for every row, because 1 is never 0
    THEN INSERT ([Name])
         VALUES (ins.[NAME])

OUTPUT inserted.ID, ins.ID
INTO @MyCrossRef (NewId, OldId);

-- Check the result
SELECT * FROM @MyCrossRef

    Uma das coisas boas MERGEé que ele permite que você acesse as colunas de origem , bem como as tabelas internas insertede na cláusula.deletedOUTPUT

    Meu código pode conter erros, pois na verdade não testei. Minha postagem no blog de alguns anos atrás entra em um pouco mais de detalhes, incluindo o desempenho da consulta.

    • 40

  2. A cláusula de saída só pode acessar dados nas linhas de destino e constantes/variáveis, não dados de outro lugar na origem SELECT, como se você estivesse operando em um gatilho.

    https://learn.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql afirma:

    Qualquer referência a colunas na tabela que está sendo modificada deve ser qualificada com o prefixo INSERTED ou DELETED.

    Então, para obter o ID original, você precisaria incluí-lo na tabela de destino para que a cláusula de saída pudesse ecoá-lo de volta, assim:

    -- The existing table 
DECLARE @MyTable TABLE (ID INT IDENTITY(1,1), [Name] NVARCHAR(MAX), SourceID INT);

-- My data I want to insert
DECLARE @MyInsertData TABLE (ID INT, [Name] NVARCHAR(MAX));
INSERT INTO @MyInsertData ( ID,Name)
VALUES ( -1 , 'bla'),(-2,'test'),(-3,'last');

DECLARE @MyCrossRef TABLE ([NewId] INT, OldId INT);

INSERT INTO @MyTable ( [Name], SourceID )
   OUTPUT Inserted.ID, Inserted.SourceID INTO @MyCrossRef
   SELECT [NAME], ID FROM @MyInsertData INS

-- Check the result
SELECT * FROM @MyCrossRef

    embora alterar o esquema do objeto de destino possa não ser prático na sua situação, isso pode não ser aplicável.

    • 8

relate perguntas