Given a table that represents a hierarchical tree structure and has three columns
I know the lowest most node of that branch, and I want to copy that to a new branch with the same number of parents that also need to be cloned.
I am trying to write a single SQL INSERT INTO statement that will make a copy of every row that is of the same main has is part one GroupID into a new GroupID.
Example beginning table:
ID | ParentGroupID | SomeValue
------------------------
1 | -1 | a
2 | 1 | b
3 | 2 | c
Goal after I run a simple INSERT INTO statement:
ID | ParentGroupID | SomeValue
------------------------
1 | -1 | a
2 | 1 | b
3 | 2 | c
4 | -1 | a-cloned
5 | 4 | b-cloned
6 | 5 | c-cloned
Final tree structure
+--a (1)
| +--b (2)
| +--c (3)
|
+--a-cloned (4)
| +--b-cloned (5)
| +--c-cloned (6)
The IDs aren't always nicely spaced out as this demo data is showing, so I can't always assume that the Parent's ID is 1 less than the current ID for rows that have parents.
Also, I am trying to do this in T-SQL (for Microsoft SQL Server 2005 and greater).
This feels like a classic exercise that should have a pure-SQL answer, but I'm too used to programming that my mind doesn't think in relational SQL.
Try this, based on a query from Quassnoi's article Adjacency List vs Nested Sets: SQL Server:
WITH q AS
(
SELECT h.*, 1 AS level
FROM Table1 h
WHERE id = 3
UNION ALL
SELECT hp.*, level + 1
FROM q
JOIN Table1 hp
ON hp.id = q.ParentGroupID
), q2 AS (
SELECT
ID,
ParentGroupID,
SomeValue,
(SELECT MAX(level) FROM q) - level AS level
FROM q
)
INSERT INTO table1
SELECT
(SELECT MAX(ID) FROM Table1) + level + 1 AS ID,
CASE WHEN level = 0 THEN -1
ELSE (SELECT MAX(ID) FROM Table1) + level
END AS ParentGroupID,
SomeValue + '-cloned'
FROM q2
Result when run on your test data:
ID ParentGroupID SomeValue
1 -1 a
2 1 b
3 2 c
4 -1 a-cloned
5 4 b-cloned
6 5 c-cloned
Assming that ID is an identity column, with auto assigned values. I'm doing this off the cuff, so appologies for any syntax errors. I hope the comments make the intent clear.
/* Find all ancestors for a given ID */
WITH Ancestors(ChildID, AncestorID) AS
(
SELECT id AS ChildID, id As AncestorID
FROM YourTable WHERE ParentGroupID=--1
UNION ALL
SELECT a.ChildID, d.ParentGroupID
FROM Ancestors AS a INNER JOIN
YourTable d ON d.ID = a.AncestorID
WHERE (b1.ParentGroupID <> -1))
),
/* Insert new rows for all ancestors of a given ID and save the results, so we have access to the new ID. we also have a column for the old ID. */
IDMap(ID, ParentGroupID, SomeValue, OldID) AS
{
// insert, using identity column assigned ID, and save the old ID
INSERT INTO YourTable SELECT d.ParentGroupID, d.SomeValue+"-cloned", d.ID FROM YourTable d
INNER JOIN Ancestors a ON a.ChildID = d.ID
WHERE a.AncestorID=<the ID to clone>
}
/* Now update the parentID in the inserted data to the new ID */
UPDATE YourTable
SET ParentGroupID = map.ID
FROM YouTable t INNER JOIN (SELECT * FROM IDMap) map
ON t.ParentGroupID=map.OldID
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With