SQL Server 2005: Preserve parent-child relationships when copying hierarchical data

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
     3      611           Cow           1
     4      611           Jersey Cow    3
     5      611           Plant         0
     6      611           Tree          5
     7      611           Oak           6

Should result in:

    id      ProjectID    Value         ParentID
--------------------------------------------------
     1      611           Animal        0
     2      611           Frog          1
     3      611           Cow           1
     4      611           Jersey Cow    3
     5      611           Plant         0
     6      611           Tree          5
     7      611           Oak           6
     8      612           Animal        0
     9      612           Frog          8
     10     612           Cow           8
     11     612           Jersey Cow    10
     12     612           Plant         0
     13     612           Tree          12
     14     612           Oak           13

Restrictions:

• Solution must work for SQL Server 2005. That is, we can't use MERGE (alas).


• 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!