Use SQL to clone a tree structure represented in a database
- by AmoebaMan17
Given a table that represents a hierarchical tree structure and has three columns
ID (Primary Key, not-autoincrementing)
ParentGroupID
SomeValue
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.
