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

Question

Hi

I want to copy N number of records within a table which is easy with a INSERT SELECT however after the insert I want a list showing oldId and newId. So lets say I have a SELECT statement which returns:

ID | Name
3  | Test A
4  | Test B
5  | Test C

If I do a SELECT INTO from this it inserts 3 new PKs. I want a list showing the new and old Ids e.g.

3 | 49
4 | 50
5 | 51

I know how to do this with loops, cursors, Scope_Identity(), or temp tables etc with many lines of code but was wondering if there is a cleaver way to get the new/old list in one line of SQL after the INSERT SELECT e.g.

INSERT INTO tbl(Name)
SELECT Name
FROM tbl
WHERE blah = 1
.
SELECT New_Old_Ids()

And I can't join the table to itself because "Name" is not unique so I'm looking for some sort of built in SQL feature. It's probably not possible but thought I'd ask.

I'm on SQL Server 2005.

Cheers Matthew

Answer 1

If you're willing to store the previous PK in the table, you can show both IDs with the OUTPUT clause:

DECLARE @t TABLE (
  PK INT NOT NULL IDENTITY(1,1), 
  Name VARCHAR(50), 
  PrevPK INT
)

INSERT @t (Name)
SELECT 'A' UNION ALL SELECT 'B' UNION ALL SELECT 'C' UNION ALL
SELECT 'D' UNION ALL SELECT 'E' 


INSERT @t (Name, PrevPK)
OUTPUT Inserted.PK, Inserted.PrevPK
SELECT Name, PK
FROM @t t
WHERE 0=0

/** Results **/
PK          PrevPK
----------- -----------
6           1
7           2
8           3
9           4
10          5

Answer 2

You can output the new id's using the output clause. But it can't output columns you're not inserting, like the id in the old table.

One way around that is to add an oldid column to the destination table:

declare @src table (id int identity, name varchar(50))
declare @dest table (id int identity, name varchar(50), oldid int)

insert into @src (name) values ('John')
insert into @src (name) values ('Jason')
insert into @src (name) values ('Aasif')
insert into @dest (name) values ('Larry')

insert into @dest (name, oldid)
output inserted.*
select name, id from @src

Of course, instead of the output clause, you can now also:

select id, oldid
from @dest