Note: Read Hugo's comments below, and follow the links to Conor Cunningham's blog. This solution will not yield consistent results. It happened to work for me, but a cursor is the only reliable way to solve this problem. AMW
A user asked me today to move data associated with an application from development to our production database. This involved copying data associated with an entity and housed in a parent-child set of tables. The problem was that the tables used identity columns for their keys, and the specific key values in the development database were already in use by other entities in the production database. Add to that the complication that the child database reference was ONLY to the identity key in the parent table. I needed to copy this data, generating new identity values, but maintain the relationship between parent and child that existed in the development database.
This scenario cries out "cursor", but I try to find set-based methods whenever possible. My first pass was to use the new OUTPUT clause on my insert query, storing the old ID and the new ID in the output table variable. I ran into a problem, though, in that SQL Server will only put data in the output table that is being inserted into the destination table, so the old ID isn't available. I still needed to build some way to cross-reference the two. Here's my solution, you decide whether it works for you.
First I declared two table variables.
declare @oldValues table (seq int identity, ID int)
declare @newValues table (seq int identity, ID int)
Next, I selected the ID values from the original table, sorting on the ID value and letting the identity column in my table variable generate a sequence number starting with one and incrementing by one.
INSERT INTO @oldValues
SELECT [ID]
FROM [DevDB].[dbo].[EntityParent] where EntityID = 137
ORDER BY ID
Then, I perform the insert from the DevDB into the ProdDB (no, I don't really have them on the same server, but it makes the concept clearer.) I use the OUTPUT clause in the insert statement to retain the new ID values in the table variable I declared earlier.
INSERT INTO [ProdDB].[dbo].[EntityParent]
([EntityID],[ColA],[ColB],[ColC])
OUTPUT INSERTED.[ID] as ID INTO @newValues
SELECT [EntityID],[ColA],[ColB],[ColC]
FROM [DevDB].[dbo].[EntityParent] where EntityID = 137
ORDER BY ID
Now, I've got the old ID values in @oldValues and the new ID values in @newValues, with the "seq" column the linking value between them and can now copy the child table data using the joined tables.
INSERT INTO [ProdDB].[dbo].[EntityChild]
([ParentID],[ChildA],[ChildB],[ChildC])
SELECT n.[ID] as ParentID,c.[ChildA],c.[ChildB],c.[ChildC]
FROM [DevDB].[dbo].[EntityChild] c
INNER JOIN @oldValues o
ON c.[ParentID] = o.[ID]
INNER JOIN @newValues n
ON o.[seq] = n.[seq]
Allen