We have a table representing a tree of values associated with an entity (call it a project), where the ParentID column refers to the id column of a row's parent. The id column is an auto-incremented IDENTITY column and primary key. Root nodes have a ParentID of 0.
We want to be able to clone the data for a given project and have the resulting ParentIDs refer to the appropriate new ids for the copied values, in a way that meets the restrictions described below the example.
For example, copying the data for ProjectID 611 in the below table:
id ProjectID Value ParentID
--------------------------------------------------
1 611 Animal 0
2 611 Frog 1
13 611 Cow 1
14 611 Jersey Cow 13
25 611 Plant 0
29 611 Tree 25
31 611 Oak 29
Should result in:
id ProjectID Value ParentID
--------------------------------------------------
1 611 Animal 0
2 611 Frog 1
13 611 Cow 1
14 611 Jersey Cow 13
25 611 Plant 0
29 611 Tree 25
31 611 Oak 29
32 612 Animal 0
33 612 Frog 32
34 612 Cow 32
35 612 Jersey Cow 34
36 612 Plant 0
37 612 Tree 36
38 612 Oak 37
Restrictions:
- Solution must work for SQL Server 2005. That is, we can't use MERGE (alas).
- We're not comfortable making assumptions about ids or how they compare to ParentIDs; the solution should apply, in principle, to ids/ParentIDs that are uniqueid, for example.
- We'd rather not add an additional column to the table. (My current solution adds an "OldId" column, which the copy procedure sets when copying rows. So I'm currently using a combination of INSERT-SELECT and UPDATE-FROM, joining the OldId column on the ParentID column to get the new id.) We'd rather not pepper all of our hierarchical tables with OldId columns just to support this copy operation.
- Solution must be reasonably performant; my initial solution was going to be a complicated set of rescursive function calls and loops processing one item at a time. I quickly abandoned that route!