問題描述
我有一個構成主題結構的相鄰列表層次模型
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模板網!