TSQL Parent > Child > Sub-Child duplication without cursor

Question

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 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

Halftimbered

Sorry the formatting looks bad, the copy/paste into SO isn't so easy. – Halftimbered

Do you have any candidate keys (unique column) in TBLACStages other than ACStageID? – Tosch

No, the only guaranteed unique column is the PK, which is ACStageID – Halftimbered

How about a composite key (multiple columns when combined are unique) – Tosch

Nothing except when combined with the PK would be absolutely unique. – Halftimbered

Answer 1

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

Bennink

Do you think three nested MERGE statements will be faster than a CURSOR over a max of 20 rows? Espescially now given the overhead of two read-in temporary tables (Parent and Child) instead of the one in my solution (Child) ? – Halftimbered

@Matthew: The MERGE statements are in fact mere INSERTS (INSERT doesn't allow returning old id, so you have to use MERGE). – Bennink

@Matthew: you can reuse the temporary table in my solution too. One query against a 20-record table will be definitely faster than 20 queries against separate records. You should always use set-based solutions if possible. – Bennink

Yes, I see that you have created INSERT s using the MERGE only because you want them to OUTPUT something. However, your solution using MERGE declared and reads-in (using OUTPUT) two temporary tables which you then JOIN to facilitate the next merge. I am not convinced this is a faster solution over a CURSOR when the recordsets are as small as mine. – Halftimbered

@Quassnoi: I know that set-based solutions are almost always better solutions than cursors. However, when you consider that each MERGE is technically an EXISTS and two INSERTS I wasn't sure it would be faster – Halftimbered

@Matthew: the CURSOR solution (which, by the way, creates a temporary table behind the scenes) will have to switch execution context between SQL and cursor operation (and hence copy the record data between different queries), while the set-based solution will do it within the query plan (which is optimized for such things). For only 20 records this of course will hardly be noticeable (in absolute times which in both cases will be measured in milliseconds), but set-based operations will be significantly faster in relative terms. – Bennink

@Matthew: there will be 1 (one) INSERT and 0 (zero) EXISTS per MERGE statement. 1 = 0 will be optimized away. – Bennink

@Quassnoi: I know that the CURSOR solution creates a temp table too, but only via a single INSERT I guess I am trying to compare the number of queries required to fulfill your MERGE operations in comparison to the number of queries potentially executed using my CURSOR solution. – Halftimbered

@Quassnoi: Ah yes, I see that the 1 = 0 will be cleared, but each MERGE still processes two inserts: one into the real table and another into the temp table. – Halftimbered

@Matthew: the same with the CURSOR solution. OPEN CURSOR will create and fill the hidden temporary table, INSERT will read from the temp table and populate the real table. – Bennink

@Quassnoi: I'm probably going to tweak the MERGE a bit but thank you for the basic framework. I've pasted my adaptation of your answer below and marked your response. – Halftimbered

Note I tried this sample code and it appears NOT to work. I'm using SQL Server 2014. When I run this code, I see the duplicates in the parent table. There should only be two rows (Parent 1 and Parent 2) in there, right? I see four rows... e.g. 1 - Parent 1 2 - Parent 2 3 - Parent 1 4 - Parent 2 – Snug

just be carefull with the "select * from @parent" and "select * from @child" this causing executeScalar not returning the return value (I am using petapoco) – Bevash

Answer 2

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

Halftimbered

Answer 3

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

Pandarus

Answer 4

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

...

Nuriel

Maybe I do not understand your answer... FAST_FORWARD is, by definition, read-only – Halftimbered

Ops you are right, i type too fast and i didnt notice you set the cursor as FAST_FORWARD. Btw i suggest instead to insert your records in TBLACStages, TBLACUpgrade, ... directly, insert them in a temp table as @TBLACStages, and then when the loop is finish, with a only 1 SELECT INTO you can put all records from @TBLACStages > TBLACStages in one shot. This reduce a lot the work of SQL Server because the TEMP TABLE are in memory and are not written on the disk. I used this tecnique as in your case. This is just a tip. – Nuriel