Create Duplicate records with autonumber foreign keys

Question

(using SS2008) Within a sql server database, I'd like to copy all the data of one client for a new client. In other words, generate exact duplicates of all records that pertain to client #1, except now the client ID field of the new records refer to client #2. This is all inside the same database.

Normally I would do this with a series of INSERT commands on the relevant tables selecting the client #1 records. However, some of the tables have autonumber ID columns, and these IDs are referenced as foreign keys in child tables. So when generating the child table records, I'd need to know and refer to the newly created autonumber Ids.

What is the cleanest way to go about this? Can it be done with SQL Server replication? My knowledge of SQL Server is pretty moderate.

You can use merge and output to create a mapping table between the original id and the new id. Have a look at this question. http://stackoverflow.com/questions/5365629/using-merge-output-to-get-mapping-between-source-id-and-target-id — Mikael Eriksson, Sep 16 '11 at 19:29

Dan Short · Accepted Answer · 2011-09-16T19:25:41.250

5

I would do something like this:

-- Set up a placeholder for the new id
DECLARE @NewID INT;

-- INSERT parent record
INSERT INTO myTable (field1, field2)
SELECT field1, field2 FROM myTable WHERE ID = 1

-- Get the new ID
SET @NewID = (SELECT SCOPE_IDENTITY());

-- Insert child records with new id 
INSERT INTO OtherTable (fkid, field1, field2)
SELECT @NewID, field1, field2 FROM OtherTable WHERE ID = 1

Now if we need to deal with thousands of records, this could work:

-- Add a new column in the database to manage where the record came from
ALTER TABLE myTable ADD ParentID int NULL

-- INSERT parent record
INSERT INTO myTable (field1, field2, ParentID)
SELECT 
    field1
    , field2
    , ID 
FROM myTable 
WHERE SomeCondition IS True

-- Insert child records with new id 
INSERT INTO OtherTable (fkid, field1, field2)
SELECT 
    myTable.ID
    , OtherTable.field1
    , OtherTable.field2 
FROM 
    OtherTable 
    INNER JOIN myTable ON OtherTable.FKID = myTable.ParentID

-- Once unneeded, drop the temporary column
-- ALTER TABLE myTable DROP COLUMN ParentID

edited Sep 16 '11 at 19:25

answered Sep 16 '11 at 18:28

Dan Short

9,598
2
28
53

Thanks. Sorry if this is obvious, but how would I generalize this approach for copying many (thousands) of records at once, each with a unique autonumber id and child records? – Tekito Sep 16 '11 at 19:05
I would do this in a cursor, or potentially create an additional column on the table to store the ID of the record the new one was copied from. You could then do a massive insert of the initial duplicate records, and then go pick up their new IDs through the new ParentID column... – Dan Short Sep 16 '11 at 19:20
I added another example to my answer or performing this type of work with a temporary column for storing the parent ID> – Dan Short Sep 16 '11 at 19:26

Create Duplicate records with autonumber foreign keys

1 Answers1