--示例数据
create table t1(id int identity,fid int,Name varchar(10))
insert t1 select 0,'1'
union all select 1,'2'
union all select 1,'3'
union all select 2,'4'
union all select 2,'5'
union all select 4,'6'
union all select 6,'7'
create table t2(id int identity,fid int,Name varchar(10))
insert t2 select 0,'a'
union all select 1,'b'
union all select 2,'c'
union all select 2,'d'
union all select 3,'e'
union all select 5,'f'
GO
--节点复制处理函数
CREATE FUNCTION f_CopyNode(
@ID int, --复制此节点下的所有子节点
@fid int, --将@ID下的所有子节点复制到此节点下面
@NewID int=NULL --新编码的开始值，如果指定为NULL，则为表中的最大编码+1
)RETURNS @t TABLE(OldID int,ID int,fid int,Level int)
AS
BEGIN
IF @NewID IS NULL
SELECT @NewID=MAX(ID) FROM T1
ELSE
SET @NewID=@NewID-1
DECLARE @Level int
SET @Level=1
INSERT @t(OldID,fid,Level) SELECT ID,@fid,@Level
FROM t2
WHERE fid=@ID
WHILE @@ROWCOUNT>0
BEGIN
UPDATE @t SET @NewID=@NewID+1,ID=@NewID
WHERE Level=@Level
SET @Level=@Level+1
INSERT @t(OldID,fid,Level) SELECT a.ID,b.ID,@Level
FROM t2 a,@t b
WHERE a.fid=b.OldID
AND b.Level=@Level-1
END
RETURN
END
GO
--调用函数将节点1下面的所有子节点复制到节点5下面
SET IDENTITY_INSERT t1 ON
INSERT t1(ID,fid,Name)
SELECT a.ID,a.fid,b.name
FROM f_CopyNode(3,5,DEFAULT) a,t2 b
WHERE a.OldID=b.ID
SET IDENTITY_INSERT t1 OFF
SELECT * FROM t1
/*--结果
id fid Name
----------- ----------- ----------
1 0 1
2 1 2
3 1 3
4 2 4
5 2 5
6 4 6
7 6 7
8 5 e
9 8 f
（所影响的行数为 9 行）
--*/
go
drop table t1,t2
drop function f_CopyNode
