Get the original and new identity mapping from the SELECT INSERT statement using the 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 data using the SELECT INSERT statement:

INSERT INTO MyTable (Name)
SELECT Name FROM MyTable

and here is part of the trick - I want to get a mapping table between the original identifier and the new identifier:

DECLARE @idsMap TABLE (OriginalId int, NewId int)

I know, I suppose to use the OUTPUT clause , but for some reason it does not 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 be inserted using select return multiple identity?
Can I insert with a table parameter and also get identifier values?

Answer 1

MERGE INTO 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;

Answer 2

MyTable? , - . , , , , .

- .

alter table MyTable
add OldID int null;

INSERT INTO MyTable (Name, OldID)
SELECT Name , Id
FROM MyTable t

select * from MyTable