如何使用层次结构复制分支？

Question

我有一张带有字段的简单桌子：

catalog_id hierarchyid NOT NULL UNIQUE,
item_id int NOT NULL identity(1,1),
item_name nvarchar(50) NOT NULL,    

​

​

我需要创建一个存储过程，它将把分支复制到另一个父程序。

例如，如下所示：

​

​

我已经完成了将一个分支从一个分支转移到另一个分支的过程，如下所示：

ALTER PROCEDURE [dbo].[move_child]
@ChildId int,
@NewParentId int
AS 
DECLARE @nold hierarchyid, @nnew hierarchyid
SELECT @nold = catalog_id FROM CatalogsTree WHERE item_id = @ChildId 

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
SELECT @nnew = catalog_id FROM CatalogsTree WHERE item_id = @NewParentId

SELECT @nnew = @nnew.GetDescendant(max(catalog_id), NULL) 
FROM CatalogsTree WHERE catalog_id.GetAncestor(1)=@nnew ;

UPDATE CatalogsTree  
SET catalog_id = catalog_id.GetReparentedValue(@nold, @nnew)
WHERE catalog_id.IsDescendantOf(@nold) = 1 ;

COMMIT TRANSACTION

但是现在我需要用相同的层次结构和item_name复制一个副本。

Answer 1

我用临时桌子做的

ALTER PROCEDURE [dbo].[copy_child]
@folder_id int,
@new_parent_id int
AS
BEGIN

创建临时表：

create table #temp_table(
    catalog_id hierarchyid NOT NULL UNIQUE,
    item_id int NOT NULL,           
    item_name nvarchar(50) NOT NULL
)

将分支复制到临时表：

declare @FolderId hierarchyid
select @FolderId = (select catalog_id from CatalogsTree where item_id = @folder_id)
INSERT INTO #temp_table (catalog_id, item_id, item_name)
SELECT catalog_id, item_id, item_name FROM CatalogsTree Where catalog_id.IsDescendantOf(@FolderId) = 1

更改catalog_id：

DECLARE @nold hierarchyid, @nnew hierarchyid
SELECT @nold = catalog_id FROM CatalogsTree WHERE item_id = @folder_id

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
SELECT @nnew = catalog_id FROM CatalogsTree WHERE item_id = @new_parent_id

SELECT @nnew = @nnew.GetDescendant(max(catalog_id), NULL) 
FROM CatalogsTree WHERE catalog_id.GetAncestor(1) = @nnew ;

UPDATE #temp_table    
SET catalog_id = catalog_id.GetReparentedValue(@nold, @nnew)
WHERE catalog_id.IsDescendantOf(@nold) = 1 ;
COMMIT TRANSACTION

将更改后的值复制到主表：

INSERT INTO CatalogsTree(catalog_id, item_name)
SELECT catalog_id, item_name FROM #temp_table

drop table #temp_table
END