Sample Header Ad - 728x90

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