TSQL Parent > Child > Sub-Child duplication without cursor

I am creating a SQL 2008 R2 stored procedure to duplicate a row and all it's children.

It's a 3-tiered setup with a Parent, Child and Sub-Child Given the ID of the parent I need to create a duplicate.

I have solved it using a fast_forward cursor.

I know I can also do it with a while loop through rows but I do not believe that will be faster than this cursor method. What are your thoughts?

Is there a better way to accomplish this task without using cursors?

EDIT: Another option I considered was creating a temp table holding the old / new PKID's of the TBLACStages records.

TBLACStages may have anywhere from 1 to 20 corresponding rows (and TBLACUpgrade will likely have 3 rows per TBLACStages row)

CREATE PROCEDURE [dbo].[spDuplicateACUnit]
@pACUnitID bigint = 0 
AS BEGIN
SET NOCOUNT ON;

DECLARE @NewACUnitID bigint = 0

INSERT INTO TBLACUnits ([col1] ,[col2] ,[...] ,[coln]) SELECT [col1] ,[col2] ,[...] ,[coln] FROM TBLACUnits WHERE ACUnitID = @pACUnitID

SELECT @NewACUnitID = SCOPE_IDENTITY()

DECLARE @ACStageID bigint = 0 
    DECLARE @NewACStageID bigint = 0

DECLARE @ACUnitCursor CURSOR

SET @ACUnitCursor = CURSOR LOCAL开发者_Go百科 FAST_FORWARD FOR SELECT ACStageID FROM TBLACStages WHERE TBLACStages.ACUnitID = @pACUnitID

OPEN @ACUnitCursor

FETCH NEXT FROM @ACUnitCursor INTO @ACStageID

WHILE @@FETCH_STATUS = 0 
BEGIN

INSERT INTO TBLACStages ([ACUnitID] ,[col1] ,[col2] ,[...] ,[coln]) SELECT @NewACUnitID ,[col1] ,[col2] ,[...] ,[coln] FROM TBLACStages WHERE TBLACStages.ACStageID = @ACStageID

SELECT @NewACStageID = SCOPE_IDENTITY()

INSERT INTO TBLACUpgrade ([ACStageID] ,[col1] ,[col2] ,[...] ,[coln]) SELECT @NewACStageID ,[col1] ,[col2] ,[...] ,[coln] FROM TBLACUpgrade WHERE TBLACUpgrade.[ACStageID] = @ACStageID

FETCH NEXT FROM @ACUnitCursor INTO @ACStageID 
END

CLOSE @ACUnitCursor DEALLOCATE @ACUnitCursor

END

GO

This should give you the idea:

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 @parent TABLE (oid INT, nid INT)
DECLARE @child 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    @parent;
SELECT  *
FROM    @parent
MERGE
INTO    t_child
USING   (
        SELECT  c.id, p.nid, c.value
        FROM    @parent 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    @child;
SELECT  *
FROM    @child;
INSERT
INTO    t_grandchild (child, value)
SELECT  c.nid, gc.value
FROM    @child c
JOIN    t_grandchild gc
ON      gc.child = c.oid
SELECT  *
FROM    t_grandchild

Ok, this is the MERGE I've come up with based on Quassnoi's solution. I should work appropriately without the CURSOR

DECLARE @parent TABLE (oid BIGINT, nid BIGINT)
DECLARE @child TABLE (oid BIGINT, nid BIGINT)

MERGE
INTO    TBLACUnits T
USING   (SELECT [col1], [...], [coln] FROM TBLACUnits WHERE ID = @pID) S

ON      1 = 0
WHEN NOT MATCHED THEN
INSERT  ([ACUnitID]
   ,[col1]
   ,[...]
   ,[coln])
VALUES  (S.[ACUnitID]
   ,S.[col1]
   ,S.[...]
   ,S.[coln]])
OUTPUT  S.ACUnitID, INSERTED.ACUnitID
INTO    @parent;

MERGE
INTO    TBLACStages T
USING   (
  SELECT  tt.[nid] 
                       ,TBLACStages.[col1]
                       ,TBLACStages.[...]
                       ,TBLACStages.[coln]
  FROM TBLACStages
  JOIN @parent tt ON tt.oid = TBLACStages.ACUnitID
  ) S
ON      1 = 0
WHEN NOT MATCHED THEN
INSERT  ([ACUnitID]
   ,[col1]
   ,[...]
   ,[coln])
VALUES  ([nid]
   ,[col1]
   ,[...]
   ,[coln])
OUTPUT  S.[ACStageID], INSERTED.[ACStageID]
INTO    @child;

INSERT INTO TBLACUpgrade 
([ACStageID]
   ,[col1]
   ,[...]
   ,[coln])
SELECT  c.[nid]
   ,TBLACUpgrade.[col1]
   ,TBLACUpgrade.[...]
   ,TBLACUpgrade.[coln]
FROM    @child c
JOIN    TBLACUpgrade
 ON      TBLACUpgrade.ACStageID  = c.oid

I saw this post I nearly gasped at the complexity, but sure it looks good. When I need to clone or copy tables with children or grandchildren I simply add a new column to the table called PreCloneControl, then reference this field in the child query of the new table to quickly and easily look up the old parent data. Easy. But if you do not have access to add columns to the table a quick hack can often serve. One example is a Last Modified User field, often an nvarchar of 100 or so character. Typically we need to update this field anyway, so drop your old control number in there and away you go. Just remember to perform a quick update on the Last Modified User field when you are done. Here is sample, I am using Temp Tables for Testing but you should use real tables.

Declare @OldControl int = 123456

Declare @TT1 Table
(
TT1Control [int] IDENTITY(1,1) NOT NULL,
SomeData nvarchar(20)
)

insert into @TT1
(
SomeData
)
Select SomeDate from LiveTable where LTControl = @OldControl


Declare @NewControl int = SCOPE_IDENTITY()


Declare @TempTT2 Table
(
TT2Control int IDENTITY(1,1) NOT NULL,
TT2TT1FKControl int,
TT2ChildData nvarchar(20),
TT2ModUser nvarchar(100)
)

insert into @TempTT2
(
TT2TT1FKControl,TT2ChildData,TT2ModUser
)
Select @NewControl, TT2ChildData, Cast(TT2Control as nvarchar(100))
From TT2 where TT2TT1FKControl = @OldControl

Select * from @TempTT2

Declare @TT3 Table
(
TT3Control int IDENTITY(1,1) NOT NULL,
TT3TT2FKControl int,
TT3GrandChildData nvarchar(50),
TT3OldTT2Control int
)

Insert Into @TT3
(
TT3TT2FKControl,TT3GrandChildData,TT3OldTT2Control
)
Select t.TT2Control, BookItemItemNumber,TT2.TT2Control
From TT2 inner join GrandChildTable on TT2Control = GCTFKControl
       ,@TempTT2 as t
Where
TT2TT1FKControl = @OldControl
and  t.TT2ModUser = Cast(TT2Control as nvarchar(100))

Select * From @TT3

Update @TempTT2 set TT2ModUser = 'UserName' Where TT2TT1FKControl = @NewControl

Select * from @TempTT2

To increase the speed of your SP you can add another statement FOR READ ONLY

So your SP will be like that:

    ...

SET @ACUnitCursor = CURSOR LOCAL FAST_FORWARD FOR 

SELECT ACStageID FROM TBLACStages WHERE TBLACStages.ACUnitID = @pACUnitID

FOR READ ONLY  -- add this to increase the speed

OPEN @ACUnitCursor

FETCH NEXT FROM @ACUnitCursor INTO @ACStageID

...