INSERT multiple lines and source (source) OUTPUT values

Question

I would like INSERT multi-line strings (using INSERT SELECT ) and OUTPUT all new and old identifiers in the mapping table.

How can I get the source id (or any source values) in an OUTPUT clause? I see no way to get any initial values.

Here is an example of a minimal code:

 -- 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 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 in the data table to temporarily save the "source identifier", and I cannot uniquely identify the elements with nothing other than the "ID" column.

Answer 1

Interest Ask. For your example, a possible trick should depend on the fact that you double the number of lines. Assuming the rows are never deleted and the [id] column remains tight:

 -- 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