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.

Shame you aren't on 2008

Martin Smith
– Martin Smith

2012-01-14 14:25:42 +00:00
Commented Jan 14, 2012 at 14:25 — Martin Smith
– Martin Smith, Commented Jan 14, 2012 at 14:25

HABO · Accepted Answer · 2012-01-15 04:47:29Z

2

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

edited Jan 15, 2012 at 4:47

answered Jan 15, 2012 at 4:31

HABO

15.9k5 gold badges44 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

INSERT multiple rows and OUTPUT original (source) values

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related