Use SQL to clone a tree structure represented in a database

Posted by AmoebaMan17 on Stack Overflow See other posts from Stack Overflow or by AmoebaMan17
Published on 2010-05-13T20:45:05Z Indexed on 2010/05/13 21:14 UTC
Read the original article Hit count: 219

Filed under:
|
|
|

Given a table that represents a hierarchical tree structure and has three columns

  1. ID (Primary Key, not-autoincrementing)
  2. ParentGroupID
  3. 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.

© Stack Overflow or respective owner

Related posts about sql

Related posts about tsql