I have a requirement to allow a user to create a copy of some records in the database. These are not exact duplicates but rather the idea is that some of the data can be copied over into another group and maybe the user can then edit that data if necessary. So I have a set of IDs I want to look up and copy from. I do something like this:

insert into Thing (Name, Desc, Group_id)
select Name, Desc, <new_group_id>
from Thing
where Thing_id in (<existing_thing_ids>);

This would create a copy of each selected thing in the newly created group.

I also need to copy over related data into other tables for each thing. So I then want to do something like this:

insert into Attribute (Thing_id, Att_Name, Att_Desc)
select t.New_Thing_id, Att_Name, Att_Desc
from Attribute a
inner join @thing_map t on a.Thing_id = t.Old_Thing_id;

What I'm trying to do here is give each new thing the equivalent attributes of the 'old' thing that it is a copy of. That is, I want to copy the attributes as well as the things.

The Thing_id is an identity column, so I don't know the IDs until after they are inserted. I need to somehow map the IDs from the old things to the new things.

I thought I might be able to use output with insert:

declare @thing_map table (Old_Thing_id int, New_Thing_id int);
insert into Thing (Name, Desc, Group_id)
output Thing_id, inserted.Thing_id into @thing_map
from Thing
where Thing_id in (<existing_thing_ids>);

However, it seems that I cannot use a non-inserted value in the output clause.

The other option is I could join on the name and description instead, which will probably work in most cases but there is no guarantee that these fields will be unique. e.g.

insert into Attribute (Thing_id, Att_Name, Att_Desc)
select new_thing.Thing_id, Att_Name, Att_Desc
from Attribute a
inner join Thing old_thing on a.Thing_id = old_thing.Thing_id
inner join Thing new_thing on new_thing.Name = old_thing.Name and new_thing.Desc = old_thing.desc;

I wonder if anyone has a good method for handling something like this?