Korzystanie z kolumn źródłowych w klauzuli OUTPUT INTO instrukcji INSERT (SQL Server)

Piszę instrukcję wsadową przetwarzania wsadowego i chciałbym użyć tabeli tymczasowej, aby śledzić wstawione identyfikatory zamiast samodzielnie zapętlać elementy i wywoływać SCOPE_IDENTITY () dla każdego wstawionego wiersza.

Dane, które należy wstawić, mają (tymczasowy) identyfikator łączący je z innymi danymi, które również powinny zostać wstawione do innej tabeli, więc potrzebuję odsyłacza do rzeczywistego identyfikatora i tymczasowego identyfikatora.

Oto przykład tego, co mam do tej pory:

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

Problem polega na tym, że nie mogę uzyskać klauzuli OUTPUT INTO, aby zaakceptować identyfikator, próbowałem @MyInsertData.IDi inne sztuczki przyłączyły się do samej tabeli, ale nic nie działa.

W rzeczywistości możesz osiągnąć to samo, zmieniając INSERTna MERGE. Chociaż MERGEinstrukcja jest w rzeczywistości całkiem fajnym sposobem wykonywania „aktualizacji” w SQL Server, nic nie stoi na przeszkodzie, abyś używał jej tylko do wstawiania:

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

Jedną z fajnych rzeczy MERGEjest to, że umożliwia dostęp do kolumn źródłowych, a także do wbudowanych insertedi deletedtabel w OUTPUTklauzuli.

Mój kod może zawierać błędy, ponieważ tak naprawdę go nie testowałem. Mój post na blogu sprzed kilku lat zawiera bardziej szczegółowe informacje, w tym dotyczące wydajności zapytań.

Klauzula wyjściowa może uzyskiwać dostęp tylko do danych w docelowych wierszach i stałych / zmiennych, a nie do danych z innego źródła SELECT, tak jak w przypadku wyzwalacza.

https://docs.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql stwierdza:

Wszelkie odniesienia do kolumn w modyfikowanej tabeli muszą być kwalifikowane z prefiksem WSTAWIONY lub USUWANY.

Aby uzyskać oryginalny identyfikator, należy go uwzględnić w tabeli docelowej, aby klauzula wyjściowa mogła go ponownie wywołać:

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

chociaż zmiana schematu obiektu docelowego może nie być praktyczna w twojej sytuacji, więc może nie mieć zastosowania.