sql - How can I clone records in a table and know the new and old ID? -

I want to copy the number of N records inside a table which is easy with INSERT SELECT, but after inserting I want a list showing old and new IDs, then say I have a selection statement that:

  id. Name 3 Test A4 | Test B5 | Test C  

If I make a selection from this then it adds 3 new PKs. I need a list that looks new and old ID.

  3 | 49 4 50 5 | 51  

I know how to do this with floating tables etc. with loops, cursors, Scope_Identity (), or multiple lines of code, but wondering what's new / INSERT SELECT e.g. After the old list

  TBL (name) in a row of SQL, select TLL by name WHERE blah = 1 Select new_Old_Ids ()  

And I can not include myself in the table because "name" is not unique, so I'm looking for some kind of built in SQL feature. Maybe this is not possible but I thought I would ask.

I am on SQL Server 2005.

Cheers Mathew

You can change the new ID to output You can output using the segment but they can not produce the output columns in that column, which you are not going to insert in the old table like the ID.

A method around this place is to add the old column to the destination table: @src table (ID int identity, name varchar (50)) Announce @dist table (id int Enter the name, 'name' ('Larry'), enter @src (name) value in the @sest (name) value ('asif') in the name, varchar (50), old edit) @src (name) ( @ John) name, old man) output was inserted. * Select the name, id @rrc

Of course, instead of the Output section, you can still:

 < Code> choose ID, old @ @