复制层次结构数据时保留父子关系

CTE与可以很好地配合使用

MERGE

下面显示了如何克隆项目（具有多棵树）并修正父项以将新森林与旧森林分开。请注意，它不依赖于ID的任何特定排列，例如，它们不必是密集的，单调递增的…。

-- 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;