Using output to capture auto increment identity, plus past identity mapping table

Question

I am trying to make exact copies of data in SQL, with new clientIDs, but keep the existing data in the old client as well. I will be inserting the data into a table with an auto incrementing integer primary key ID. I need to retain the ID's of the Old records and the new records together so I can continue using this mapping as I copy the different table data so I can maintain relationships.

At this point I have the following:

INSERT INTO [dbo].[Driver]
OUTPUT inserted.ID
       inserted.Name
       inserted.ClientID
SELECT Name,
       1234 AS clientID
FROM dbo.Driver

I am wondering if there is a way to also select the old ID of the driver in the Output so I can then insert all of this into a holding table using the OUTPUT. So I need to end up with the following after I perform the insert into the dbo.Driver table so I can also insert these values into a temp table:

NewID
OldID
Name
ClientID

At this point I don't know of a way to pull the Original ID from the original record.

score 0 · Answer 1 · edited Aug 10 '15 at 13:10

0

you can try...

INSERT INTO dbo.Driver (oldID, Name, clientID)
SELECT
   B.ID,
   A.Name,
   1234 AS clientID
FROM dbo.Driver A
LEFT JOIN dbo.Driver B ON A.Name = B.Name AND A.clientID = b.clientID

or maybe just

INSERT INTO dbo.Driver (oldID, Name, clientID)
SELECT
    ID,
    Name,
    1234 AS clientID
FROM dbo.Drive

edited Aug 10 '15 at 13:10

Peter Albert

16,917
5
64
88

answered May 14 '14 at 00:30

Batty McBat

120
1
8

score 0 · Accepted Answer · edited May 23 '17 at 11:44

0

I ended up using MERGE INTO to keep track of the old ID as per the following SO post: How to use OUTPUT to capture new and old ID?

edited May 23 '17 at 11:44

Community

1
1

answered Jun 05 '14 at 13:22

mameesh

3,651
9
37
47

Using output to capture auto increment identity, plus past identity mapping table

2 Answers2