How to copy a nested sub-tree from one node to another

  • I have a tree and I need to copy a nested sub-tree (an element with its children, which in turn may have their owns) from one place to another.

    The system should allow to handle up to 8 levels. I do know how to move, but cannot figure out how to copy.

    Can one advise how to do it or point me to an example, please?

    Much appreciated

    Below is a working example With Create Table, Select and Cut / Paste (implemented via Update).

    I would like to know how to copy a nested tree with reference id 4451 from Parent_Id 1 to Parent_Id = 2

    --***** Table Definition With Insert Into to provide some basic data ****

    IF (OBJECT_ID ('myRefTable', 'U') IS NOT NULL)

    DROP TABLE myRefTable;

    GO

    CREATE TABLE myRefTable

    (

    Reference_Id INT DEFAULT 0 NOT NULL CONSTRAINT myRefTable_PK PRIMARY KEY,

    Reference_Code VARCHAR(20),

    Reference_Desc NVARCHAR(50) NOT NULL,

    Parent_Id INT REFERENCES myRefTable(Reference_Id));

    WITH someData AS (

    SELECT 1 AS RefId,

    'REF1' AS RefCode,

    'Reference 1' AS RefDesc,

    NULL AS ParentId

    UNION ALL

    SELECT 2 AS RefId,

    'REF2' AS RefCode,

    'Reference 2' AS RefDesc,

    NULL AS ParentId

    UNION ALL

    SELECT 7897 AS RefId,

    'ABC1' AS RefCode,

    'Application Process 1 ' AS RefDesc,

    1 AS ParentId

    UNION ALL

    SELECT 4451 AS RefId,

    'ABC2' AS RefCode,

    'Application Process 2' AS RefDesc,

    1 AS ParentId

    UNION ALL

    SELECT 91 AS RefId,

    'CBC1' AS RefCode,

    'Consideration Process 1' AS RefDesc,

    4451 AS ParentId

    UNION ALL

    SELECT 781 AS RefId,

    'DBC1' AS RefCode,

    'Decision Process 1' AS RefDesc,

    91 AS ParentId

    )

    INSERT INTO myRefTable

    SELECT * FROM someData;

    --****** End of Table Defintion *****---

    --**** The following recursive SELECT shows the nested tree from Reference_Id 4451 onwards *****--

    WITH TreeRef_CTE AS (

    SELECT Reference_Id,

    Reference_Code,

    Reference_Desc,

    Parent_Id

    FROM myRefTable m

    WHERE m.Reference_Id = 4451

    UNION ALL

    SELECT m.Reference_Id,

    m.Reference_Code,

    m.Reference_Desc,

    m.Parent_Id

    FROM myRefTable m

    JOIN TreeRef_CTE t

    ON t.Reference_Id = m.Parent_Id)

    SELECT * FROM TreeRef_CTE rt OPTION (MAXRECURSION 8)

    --****** The UPDATE below moves (Cut / Paste) nested sub-tree 4451 from Parent_Id = 1 to Parent_Id = 2

    UPDATE myRefTable

    SET Parent_Id = 2

    WHERE Reference_Id = 4451;

    --***** Can one advise how to Copy nested sub-tree 4451 with all its children to Parent_Id 2, without deleting from Parent_Id = 1, please?

  • Either it's not clear what you're trying to do or you're over think it... You're working with a simple adjacency list, which means that you can easily move any node to another parent any all of it's children will follow.

    Moving 4451 from 1 to 2, not only moves 4451 under 2, it also brings 91 & 781 with it.

    If I'm misunderstanding the objective, please let me know.

  • Jason A. Long (5/12/2015)


    Either it's not clear what you're trying to do or you're over think it... You're working with a simple adjacency list, which means that you can easily move any node to another parent any all of it's children will follow.

    Moving 4451 from 1 to 2, not only moves 4451 under 2, it also brings 91 & 781 with it.

    If I'm misunderstanding the objective, please let me know.

    Yes, you are. what you have wrote is Cut / Paste not Copy / Paste.

    In my original question I have stated, I do know how to cut / paste using UPDATE example

    Thank you for looking into my query though.

  • SQL doesn't really have a concept of copy/paste vs cut/paste, so that doesn't really add a lot of clarity.

    Are trying to move 4451 under 2, all alone and have 91 attach directly to 1? Or... Kill off 91 & 781 altogether?

  • Jason A. Long (5/12/2015)


    SQL doesn't really have a concept of copy/paste vs cut/paste, so that doesn't really add a lot of clarity.

    Are trying to move 4451 under 2, all alone and have 91 attach directly to 1? Or... Kill off 91 & 781 altogether?

    Not to move 4451 under 2, but COPY 4451 to 2 with all its dependencies AND to keep the original under 1. In order to achieve it

    I need to use INSERT INTO with new reference id's as obviously I cannot utilise same 4451 due to its uniqueness.

  • Gottcha... Can the new ReferenceIDs be any INT as long as they don't already exist in in the table?

  • The following should do the trick. I left the "Reference_Desc" blank (marked as "new data") because it's redundant and can be calculated from the Reference_Code column easy enough.

    Also, left various "SELECT * FROM #TableName" in the code to make it easier to see what's going on...

    IF (OBJECT_ID ('tempdb..#myRefTable', 'U') IS NOT NULL)

    DROP TABLE #myRefTable;

    GO

    CREATE TABLE #myRefTable

    (

    Reference_Id INT DEFAULT 0 NOT NULL CONSTRAINT myRefTable_PK PRIMARY KEY,

    Reference_Code VARCHAR(20),

    Reference_Desc NVARCHAR(50) NOT NULL,

    Parent_Id INT REFERENCES #myRefTable(Reference_Id));

    WITH someData AS (

    SELECT 1 AS RefId,

    'REF1' AS RefCode,

    'Reference 1' AS RefDesc,

    NULL AS ParentId

    UNION ALL

    SELECT 2 AS RefId,

    'REF2' AS RefCode,

    'Reference 2' AS RefDesc,

    NULL AS ParentId

    UNION ALL

    SELECT 7897 AS RefId,

    'ABC1' AS RefCode,

    'Application Process 1 ' AS RefDesc,

    1 AS ParentId

    UNION ALL

    SELECT 4451 AS RefId,

    'ABC2' AS RefCode,

    'Application Process 2' AS RefDesc,

    1 AS ParentId

    UNION ALL

    SELECT 91 AS RefId,

    'CBC1' AS RefCode,

    'Consideration Process 1' AS RefDesc,

    4451 AS ParentId

    UNION ALL

    SELECT 781 AS RefId,

    'DBC1' AS RefCode,

    'Decision Process 1' AS RefDesc,

    91 AS ParentId

    )

    INSERT INTO #myRefTable

    SELECT * FROM someData;

    --****** End of Table Defintion *****---

    SELECT * FROM #myRefTable mrt;

    /* ==================================================

    Start of the actual solution

    ================================================== */

    DECLARE

    @NodeToCopy INT = 4451,

    @NodeToCopyTo INT = 2

    IF OBJECT_ID('tempdb..#NodesToCopy') IS NOT NULL

    DROP TABLE #NodesToCopy;

    WITH TreeRef_CTE AS (

    SELECT Reference_Id,

    Reference_Code,

    Reference_Desc,

    Parent_Id

    FROM #myRefTable m

    WHERE m.Reference_Id = @NodeToCopy

    UNION ALL

    SELECT m.Reference_Id,

    m.Reference_Code,

    m.Reference_Desc,

    m.Parent_Id

    FROM #myRefTable m

    JOIN TreeRef_CTE t

    ON t.Reference_Id = m.Parent_Id)

    SELECT

    rt.Reference_Id,

    SUBSTRING(rt.Reference_Code, 1, 3) AS RefCodeType,

    CAST(NULL AS INT) AS RefCodeNum,

    rt.Parent_Id

    INTO #NodesToCopy

    FROM

    TreeRef_CTE rt

    OPTION

    (MAXRECURSION 8)

    SELECT

    *

    FROM

    #NodesToCopy ntc

    ;WITH CurIDSet AS (

    SELECT

    ROW_NUMBER() OVER (ORDER BY x.ID) AS rn,

    x.ID

    FROM (

    SELECT

    ntc.Reference_Id AS ID

    FROM #NodesToCopy ntc

    UNION

    SELECT

    ntc.Parent_Id AS ID

    FROM #NodesToCopy ntc

    WHERE ntc.Parent_Id <> (SELECT ntc2.Parent_Id FROM #NodesToCopy ntc2 WHERE ntc2.Reference_Id = @NodeToCopy)

    ) x

    ), NewIDs AS (

    SELECT TOP (SELECT COUNT(*) FROM CurIDSet)

    ROW_NUMBER() OVER (ORDER BY t.N) AS rn,

    t.n

    FROM dbo.Tally t

    WHERE NOT EXISTS (

    SELECT 1

    FROM (

    SELECT mrt.Reference_Id AS id

    FROM #myRefTable mrt

    UNION ALL

    SELECT mrt.Parent_Id AS id

    FROM #myRefTable mrt

    ) x

    WHERE t.N = x.id

    )

    )

    UPDATE ntc SET

    Reference_Id = ni1.N,

    Parent_Id = ni2.N

    FROM

    #NodesToCopy ntc

    JOIN CurIDSet cs1

    ON ntc.Reference_Id = cs1.ID

    JOIN NewIDs ni1

    ON cs1.rn = ni1.rn

    LEFT JOIN CurIDSet cs2

    ON ntc.Parent_Id = cs2.ID

    LEFT JOIN NewIDs ni2

    ON cs2.rn = ni2.rn

    UPDATE #NodesToCopy SET Parent_Id = @NodeToCopyTo WHERE Parent_Id IS NULL

    SELECT * FROM #NodesToCopy ntc

    ;WITH RefCodes AS (

    SELECT

    x.RefCodeType,

    MAX(x.RefCodeNum) + 1 AS RefCodeNum

    FROM (

    SELECT

    SUBSTRING(mrt.Reference_Code, 1, 3) AS RefCodeType,

    CAST(SUBSTRING(mrt.Reference_Code, 4, 1) AS INT) AS RefCodeNum

    FROM #myRefTable mrt

    ) x

    GROUP BY

    x.RefCodeType

    )

    UPDATE ntc SET ntc.RefCodeNum = rc.RefCodeNum

    FROM

    #NodesToCopy ntc

    JOIN RefCodes rc

    ON ntc.RefCodeType = rc.RefCodeType

    SELECT * FROM #NodesToCopy ntc

    INSERT #myRefTable (Reference_Id, Reference_Code, Reference_Desc, Parent_Id)

    SELECT

    ntc.Reference_Id,

    ntc.RefCodeType + CAST(ntc.RefCodeNum AS VARCHAR(5)) AS Reference_Code,

    'new data' AS Reference_Desc,

    ntc.Parent_Id

    FROM #NodesToCopy ntc

    SELECT * FROM #myRefTable mrt

    The final result...

    Reference_Id Reference_Code Reference_Desc Parent_Id

    ------------ -------------------- -------------------------------------------------- -----------

    1 REF1 Reference 1 NULL

    2 REF2 Reference 2 NULL

    3 CBC2 new data 5

    4 DBC2 new data 3

    5 ABC3 new data 2

    91 CBC1 Consideration Process 1 4451

    781 DBC1 Decision Process 1 91

    4451 ABC2 Application Process 2 1

    7897 ABC1 Application Process 1 1

  • Thank you Celco,

    Meanwhile I have sorted it out already, but still would like understand benefits of your solution, so here are some answers to your questions / statements

    which will help me get through

    Celco. Why did you use the weird CTE/unions/Sybase style insertion?

    Boris. This is an example I have found in SqlServerCentral articles.

    Celco. Did you know that a tree has one and only one root node? You have two! Your 'REF2' is an orphan and shodul not be here.

    Boris. The original table has also account Id, which I omitted for a simplicity of the task, but .Net draws the tree very nicely, based on this table.

    Celco. The idea is to spread the (lft, rgt) numbers after the youngest child of the parent, REF1 in this case, over by two to make room for the new addition, REF2.

    Boris. I have simply used SELECT MAX(Reference_Id) and then added it to ROW_NUMBER() when inserting MAX(Referecnce_Id)+ROW_NUMBER() AS newColumn, Reference_Id

    into a session table. The last step was insert into my real table select from session table, replacing Reference_Id with newColumn and scalar sql select to replace Parent_Id with newColumn. Works very fast.

    Thank you again

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply