Retrieve original and new identities mapping from SELECT INSERT statement using OUTPUT clause

Question

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?

Answer 1

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;