問題描述

我有一個構成主題結構的相鄰列表層次模型

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

這篇關于有沒有辦法在給定起始 id 的情況下復制相鄰列表層次結構的一部分?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！