Using source columns in OUTPUT INTO clause of an INSERT statement (SQL Server)
34
votes
2
answers
33563
views
I am writing a batch processing insert statement and would like to use a temp table to keep track of inserted ID's instead of looping through the items myself and calling SCOPE_IDENTITY() for each inserted row.
The data that needs to be inserted has (temporary) ID's linking it to other data that also should be inserted into another table, so I need a cross reference of the actual Id and the temporary Id.
This is an example of what I have so far:
-- 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
The problem is that I cannot get the OUTPUT INTO clause to accept the ID, I've tried
@MyInsertData.ID
and other tricks joining the table to itself, but nothing seems to work.
Asked by Louis Somers
(593 rep)
Nov 17, 2017, 11:57 AM
Last activity: Nov 19, 2017, 05:55 PM
Last activity: Nov 19, 2017, 05:55 PM