Retrieve original and new identities mapping from SELECT INSERT statement using OUTPUT clause
output-clause, select-insert, sql-server
Solution
It can be achieved using `MERGE INTO` and `OUTPUT`:
MERGE INTO MyTable AS tgt
USING MyTable AS src ON 1=0 --Never match
WHEN NOT MATCHED THEN
INSERT (Name)
VALUES (src.Name)
OUTPUT
src.Id,
inserted.Id
INTO @idsMap;
Problem
I have a table with two columns: ``` CREATE TABLE MyTable( Id int IDENTITY(1,1) NOT NULL, Name nvarchar(100) NOT NULL); ``` I want to duplicate the data using SELECT INSERT statement: ``` INSERT INTO MyTable (Name) SELECT Name FROM MyTable ``` and here is the trickey part - I want to retrieve a mapping table between the original identity and the new identity: ``` DECLARE @idsMap TABLE (OriginalId int, NewId int) ``` I know I suppose to use the OUTPUT clause, but for some reason it doesn't work: ``` INSERT INTO MyTable (Name) OUTPUT t.Id, INSERTED.Id INTO @idsMap (OriginalId, NewId) SELECT Name FROM MyTable t -- Returns error The multi-part identifier "t.Id" could not be bound. ``` Related questions: can SQL insert using select return multiple identities? Possible to insert with a Table Parameter, and also retrieve identity values?