INSERT multiple rows and OUTPUT original (source) values

Question

I would like to INSERT multpile rows (using INSERT SELECT), and OUTPUT all the new and old IDs into a "mapping" table.

How can I get the original ID (or any source values) in the OUTPUT clause? I don't see a way to get any source values there.

Here is a minimal code example:

-- create some test data
declare @t table (id int identity, name nvarchar(max))
insert @t ([name]) values ('item 1')
insert @t ([name]) values ('another item')

-- duplicate items, storing a mapping from src ID => dest ID
declare @mapping table (srcid int, [newid] int)

insert @t ([name])
output ?????, inserted.id into @mapping-- I want to use source.ID but it's unavailable here.
select [name] from @t as source

-- show results    
select * from @t
select * from @mapping

My actual scenario is more complex, so for example I cannot create a temp column on the data table in order to store a "original ID" temporarily, and I cannot uniquely identify items by anything other than the 'ID' column.

Answer 1

Interesting question. For your example, a possible cheat is to depend on the fact that you are doubling the number of rows. Assuming that rows are never deleted and the [id] column remains dense:

-- create some test data 
declare @t table (id int identity, name nvarchar(max)) 
insert @t ([name]) values ('item 1') 
insert @t ([name]) values ('another item') 

-- duplicate items, storing a mapping from src ID => dest ID 
declare @mapping table (srcid int, [newid] int) 

declare @Rows as Int = ( select Count(42) from @t )
insert @t ([name])
  output inserted.id - @Rows, inserted.id into @mapping
  select [name] from @t as source order by source.id -- Note 'order by' clause.

-- show results     
select * from @t 
select * from @mapping