Duplicating records efficiently in tsql

Question

Hey guys,

I've a scenario where I have a parent table which has '1 to many' relationships with two or three tables. These child tables again have '1 to many' relationships with more tables and so on. This goes up to 5 to 6 levels of hierarchy.

Now, based on single primary key value of the parent table, I want to duplicate all information related to it in database. I wrote a stored procedure which uses cursors and inserts child rows one by one and sets new foreign key values with each insert. But it is consuming some time because number of records in child tables is high.

Is there any other efficient way to do this?

Answer 1

In SQL Server 2008:

CREATE TABLE t_parent (id INT NOT NULL PRIMARY KEY IDENTITY, value VARCHAR(100))
CREATE TABLE t_child (id INT NOT NULL PRIMARY KEY IDENTITY, parent INT NOT NULL, value VARCHAR(100))
CREATE TABLE t_grandchild (id INT NOT NULL PRIMARY KEY IDENTITY, child INT NOT NULL, value VARCHAR(100))

INSERT
INTO    t_parent (value)
VALUES  ('Parent 1')

INSERT
INTO    t_parent (value)
VALUES  ('Parent 2')

INSERT
INTO    t_child (parent, value)
VALUES  (1, 'Child 2')

INSERT
INTO    t_child (parent, value)
VALUES  (2, 'Child 2')

INSERT
INTO    t_grandchild (child, value)
VALUES  (1, 'Grandchild 1')

INSERT
INTO    t_grandchild (child, value)
VALUES  (1, 'Grandchild 2')

INSERT
INTO    t_grandchild (child, value)
VALUES  (2, 'Grandchild 3')

DECLARE @tt TABLE (oid INT, nid INT)

MERGE
INTO    t_parent
USING   (
        SELECT  id, value
        FROM    t_parent
        ) p
ON      1 = 0
WHEN NOT MATCHED THEN
INSERT  (value)
VALUES  (value)
OUTPUT  p.id, INSERTED.id
INTO    @tt;

MERGE
INTO    t_child
USING   (
        SELECT  c.id, p.nid, c.value
        FROM    @tt p
        JOIN    t_child c
        ON      c.parent = p.oid
        ) c
ON      1 = 0
WHEN NOT MATCHED THEN
INSERT  (parent, value)
VALUES  (nid, value)
OUTPUT  c.id, INSERTED.id
INTO    @tt;

INSERT
INTO    t_grandchild (child, value)
SELECT  c.nid, gc.value
FROM    @tt c
JOIN    t_grandchild gc
ON      gc.child = c.oid

In earlier versions of SQL Server, you will have to do a SELECT followed by an INSERT to find out the new values of the PRIMARY KEY.

Answer 2

You'll have to insert one table at a time, but you can do it by inserting sets instead of rows if you allow the FK values in the new parent's child tables to be the same as the FK values of the original parent.

Say you have a view of your parent table and in your sp you limit it to the row to copy from (pk=1, say). Then insert that row into the parent table substituting PK=2 for the PK val.

Now use a second view of one of the child tables. In your sp, limit the set of rows to those with PK=1. Again, insert all those rows into that same child table substituting PK=2 for the PK field val.