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

Sorry the formatting looks bad, the copy/paste into SO isn't so easy. — Matthew, Aug 31 '10 at 22:03
Do you have any candidate keys (unique column) in TBLACStages other than ACStageID? — Conrad Frix, Aug 31 '10 at 22:10
No, the only guaranteed unique column is the PK, which is ACStageID — Matthew, Aug 31 '10 at 22:11
How about a composite key (multiple columns when combined are unique) — Conrad Frix, Aug 31 '10 at 22:15
Nothing except when combined with the PK would be absolutely unique. — Matthew, Aug 31 '10 at 22:22

score 6 · Accepted Answer · answered Aug 31 '10 at 22:35

6

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

answered Aug 31 '10 at 22:35

Quassnoi

381,935
83
584
593

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) ? – Matthew Aug 31 '10 at 22:50
1

@Matthew: The `MERGE` statements are in fact mere `INSERTS` (`INSERT` doesn't allow returning old `id`, so you have to use `MERGE`). – Quassnoi Aug 31 '10 at 22:54
1

@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. – Quassnoi Aug 31 '10 at 22:56
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. – Matthew Aug 31 '10 at 22:57
@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 – Matthew Aug 31 '10 at 23:00
@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. – Quassnoi Aug 31 '10 at 23:04
1

@Matthew: there will be `1` (one) `INSERT` and `0` (zero) `EXISTS` per `MERGE` statement. `1 = 0` will be optimized away. – Quassnoi Aug 31 '10 at 23:06
@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. – Matthew Aug 31 '10 at 23:07
@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. – Matthew Aug 31 '10 at 23:09
@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. – Quassnoi Aug 31 '10 at 23:15
@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. – Matthew Sep 01 '10 at 00:23
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 – Carl Prothman Sep 21 '16 at 14:30
just be carefull with the "select * from @parent" and "select * from @child" this causing executeScalar not returning the return value (I am using petapoco) – kite Nov 30 '16 at 15:52

score 1 · Answer 2 · edited Dec 11 '15 at 00:45

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

Matthew · Answer 3 · 2010-09-01T15:32:56.963

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

score 0 · Answer 4 · answered Aug 31 '10 at 22:21

0

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

...

answered Aug 31 '10 at 22:21

Luka Milani

1,521
14
21

Maybe I do not understand your answer... `FAST_FORWARD` is, by definition, read-only – Matthew Aug 31 '10 at 22:23
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. – Luka Milani Aug 31 '10 at 22:45

TSQL Parent > Child > Sub-Child duplication without cursor

4 Answers4

Linked