我有一个构成主题结构的相邻列表层次模型
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 的情况下复制相邻列表层次结构的一部分?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!