sql - Preserve parent-child relationships when copying hierarchical data

Question

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!

Answer 1

A CTE works nicely with MERGE, but is problematic in SQL Server 2005. Sorry for the misleading comment earlier.

The following shows how to clone a project (with multiple trees) and fix up up the parentage to separate the new forest from the old. Note that it does not depend on any particular arrangement of Id's, e.g. they need not be dense, monotonically increasing, ... .

-- Sample data.
declare @Projects as Table
  ( Id Int Identity, ProjectId Int, Value VarChar(16), ParentId Int Null );
insert into @Projects ( ProjectId, Value, ParentId ) values
  ( 611, 'Animal', 0 ),
  ( 611, 'Frog', 1 ),
  ( 611, 'Cow', 1 ),
  ( 611, 'Jersey Cow', 3 ),
  ( 611, 'Plant', 0 ),
  ( 611, 'Tree', 5 ),
  ( 611, 'Oak', 6 );
-- Display the raw data.
select * from @Projects;

-- Display the forest.
with IndentedProjects ( Id, ProjectId, Value, ParentId, Level, Path ) as
  ( -- Start with the top level rows.
  select Id, ProjectId, Value, ParentId, 0, Convert( VarChar(1024), Right( '000' + Convert( VarChar(4), Id ), 4 ) )
    from @Projects
    where ParentId = 0
  union all
  -- Add the children one level at a time.
  select P.Id, P.ProjectId, P.Value, P.ParentId, IP.Level + 1, Convert( VarChar(1024), IP.Path + '<' + Right( '000' + Convert( VarChar(4), P.Id ), 4 ) )
    from IndentedProjects as IP inner join
      @Projects as P on P.ParentId = IP.Id
  )
  select Space( Level * 2 ) + Value as [IndentedValue], Id, ProjectId, Value, ParentId, Level, Path
    from IndentedProjects
    order by Path;

-- Clone the project.
declare @OldProjectId as Int = 611;
declare @NewProjectId as Int = 42;
declare @Fixups as Table ( OldId Int, [NewId] Int );
begin transaction -- With suitable isolation since the hierarchy will be invalid until we apply the fixups!
insert into @Projects
  output Inserted.ParentId, Inserted.Id
    into @Fixups
  select @NewProjectId, Value, Id -- Note that we save the old Id in the new ParentId.
    from @Projects as P
    where ProjectId = @OldProjectId;
-- Apply the fixups.
update PNew
  set ParentId = IsNull( FNew.[NewId], 0 )
  -- Output the fixups just to show what is going on.
  output Deleted.Id, Deleted.ParentId as [ParentIdBeforeFixup], Inserted.ParentId as [ParentIdAfterFixup]
  from @Fixups as F inner join
    @Projects as PNew on PNew.Id = F.[NewId] inner join -- Rows we need to fix.
    @Fixups as FOld on FOld.OldId = PNew.ParentId inner join
    @Projects as POld on POld.Id = FOld.OldId left outer join
    @Fixups as FNew on FNew.OldId = POld.ParentId;
commit transaction;

-- Display the forest.
with IndentedProjects ( Id, ProjectId, Value, ParentId, Level, Path ) as
  ( -- Start with the top level rows.
  select Id, ProjectId, Value, ParentId, 0, Convert( VarChar(1024), Right( '000' + Convert( VarChar(4), Id ), 4 ) )
    from @Projects
    where ParentId =0
  union all
  -- Add the children one level at a time.
  select P.Id, P.ProjectId, P.Value, P.ParentId, IP.Level + 1, Convert( VarChar(1024), IP.Path + '<' + Right( '000' + Convert( VarChar(4), P.Id ), 4 ) )
    from IndentedProjects as IP inner join
      @Projects as P on P.ParentId = IP.Id
  )
  select Space( Level * 2 ) + Value as [IndentedValue], Id, ProjectId, Value, ParentId, Level, Path
    from IndentedProjects
    order by Path;