May 12, 2015 at 3:18 pm
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?
May 12, 2015 at 5:18 pm
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.
May 12, 2015 at 5:27 pm
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.
May 12, 2015 at 5:35 pm
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?
May 12, 2015 at 5:41 pm
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.
May 12, 2015 at 5:51 pm
Gottcha... Can the new ReferenceIDs be any INT as long as they don't already exist in in the table?
May 12, 2015 at 7:00 pm
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
May 13, 2015 at 10:06 am
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