CREATE TABLE tb(ID int,PID int,Name nvarchar(10))
INSERT tb SELECT 1,NULL,'山東省'
UNION ALL SELECT 2,1?? ,'煙臺(tái)市'
UNION ALL SELECT 4,2?? ,'招遠(yuǎn)市'
UNION ALL SELECT 3,1?? ,'青島市'
UNION ALL SELECT 5,NULL,'四會(huì)市'
UNION ALL SELECT 6,5?? ,'清遠(yuǎn)市'
UNION ALL SELECT 7,6?? ,'小分市'
GO

--節(jié)點(diǎn)復(fù)制處理函數(shù)
CREATE FUNCTION f_CopyNode(
@ID???? int,??????? --復(fù)制此節(jié)點(diǎn)下的所有子節(jié)點(diǎn)
@PID??? int,?????? --將@ID下的所有子節(jié)點(diǎn)復(fù)制到此節(jié)點(diǎn)下面
@NewID? int=NULL --新編碼的開始值，如果指定為NULL，則為表中的最大編碼+1
)RETURNS @t TABLE(OldID int,ID int,PID int,Level int)
AS
BEGIN
?IF @NewID IS NULL
??SELECT @NewID=COUNT(*) FROM TB
?ELSE
??SET @NewID=@NewID-1
?DECLARE @Level int
?SET @Level=1
?INSERT @t(OldID,PID,Level) SELECT ID,@PID,@Level
?FROM tb
?WHERE PID=@ID
?WHILE @@ROWCOUNT>0
?BEGIN
??UPDATE @t SET @NewID=@NewID+1,ID=@NewID
??WHERE Level=@Level
??SET @Level=@Level+1
??INSERT @t(OldID,PID,Level) SELECT a.ID,b.ID,@Level
??FROM tb a,@t b
??WHERE a.PID=b.OldID
???AND b.Level=@Level-1
?END
?RETURN
END
GO

--調(diào)用函數(shù)將節(jié)點(diǎn)1下面的所有子節(jié)點(diǎn)復(fù)制到節(jié)點(diǎn)5下面
INSERT tb(ID,PID,Name)
SELECT a.ID,a.PID,b.Name
FROM f_CopyNode(1,5,DEFAULT) a,tb b
WHERE a.OldID=b.ID
SELECT * FROM tb
/*--結(jié)果
ID????????? PID???????? Name??????
---------------- ----------------- ----------
1?????????? NULL??????? 山東省
2?????????? 1?????????? 煙臺(tái)市
4?????????? 2?????????? 招遠(yuǎn)市
3?????????? 1?????????? 青島市
5?????????? NULL??????? 四會(huì)市
6?????????? 5?????????? 清遠(yuǎn)市
7?????????? 6?????????? 小分市
8?????????? 5?????????? 煙臺(tái)市
9?????????? 5?????????? 青島市
10????????? 8?????????? 招遠(yuǎn)市
--*/

復(fù)制指定節(jié)點(diǎn)及其所有子節(jié)點(diǎn)到指定結(jié)點(diǎn)的處理示例(借鑒方式排序法).sql