问题描述

我有一个构成主题结构的相邻列表层次模型

I have an adjacent list hierarchy model that makes up a topic structure

   ID   Parent_Id Topic_Name
   1    Null      Topic 1
   2    Null      Topic 2
   3    2            Topic 3
   4    3               Topic 4
   5    2            Topic 5
   6    Null      Topic 6

这构成了我无法更改的应用程序的一部分 - 主题没有多个父级，所以很遗憾我无法移动到嵌套集 - 尽管如果这是过程中的一个临时步骤 - 只要它返回到相邻列表层次模型

This forms part of an application which I cant change - the topics dont have multiple parents so unfortunatly I can't move to a nested sets - although if this was an interim step in the process - this would be fine as long as it went back to adjacent list hierarchy model

我想指定一个主题 id，然后将其复制到一个新的主题 id 并保留下面的级别/结构

I want to specify a topic id and then copy it to a new topic id and retain the levels / structure underneath

因此在我的示例中，我可以指定主题 topic_id 2 并且它会创建

So in my example I could specify topic topic_id 2 and it would create

   ID   Parent_Id Topic_Name
   7    Null      Topic 2
   8    7            Topic 3
   9    8               Topic 4
   10   7            Topic 5

ID 会自动编号，因此无需构建，但显然需要保留父 ID

Auto numbering is taken care of for the ID so no need to construct that, but obviously the parent id needs to be retained

我怎样才能实现上述目标?我是否需要展平数据并在每次插入后执行 3 个单独的插入来记录 ID?

How can I achieve the above? would I need to flatten the data and do 3 seperate inserts logging the id after each insert?

推荐答案

您可以使用递归 CTE 来获取要插入的行.如果您使用 merge 添加行，您可以使用 output 来捕获 生成ID和旧ID之间的映射，可用于更新列Parent_ID对于插入的行.

You can use a recursive CTE to get the rows to insert. If you use merge to add the rows you can use output to capture a mapping between the generated ID and the old ID which can be used to update the column Parent_ID for the inserted rows.

-- ID for topic to copy 
declare @ID int;
set @ID = 2;

-- Table to hold the inserted rows
declare @T table
(
  New_ID int,
  Old_ID int,
  Old_ParentID int
);

-- Add rows from recursive CTE using merge
with C as
(
  select T.ID, T.Parent_Id, T.Topic_Name
  from YourTable as T
  where T.ID = @ID
  union all
  select T.ID, T.Parent_Id, T.Topic_Name
  from YourTable as T
    inner join C 
      on C.ID = T.Parent_Id
)
merge YourTable
using C
on 0 = 1
when not matched then
  insert (Topic_Name) values (C.Topic_Name)
output inserted.ID,
       C.ID,
       C.Parent_Id
  into @T(New_ID, Old_ID, Old_ParentID);

-- Update Parent_Id for the new rows
update Y set
  Parent_Id = T2.New_ID
from @T as T1
  inner join @T as T2
    on T1.Old_ParentID = T2.Old_ID
  inner join YourTable as Y
    on T1.New_ID = Y.ID;

SE-Data