CREATE TABLE TreeShip(
[ID] uniqueidentifier PRIMARY KEY NOT NULL,
[Name] [nvarchar](50) NULL,
[Remark] [nvarchar](500) NULL,
[ParentID] uniqueidentifier not null
)
GO
INSERT INTO TreeShip(ID,Name,Remark,ParentID) values ('47B9AF56-32D8-4561-8096-15D832C47B26','上海一中','源数据','00000000-0000-0000-0000-000000000000')
INSERT INTO TreeShip(ID,Name,Remark,ParentID) values ('64F41586-449A-464B-BB48-FF13FBC492BC','上海二中','源数据','00000000-0000-0000-0000-000000000000')
INSERT INTO TreeShip(ID,Name,Remark,ParentID) values (newid(),'上海三中','源数据','00000000-0000-0000-0000-000000000000')
INSERT INTO TreeShip(ID,Name,Remark,ParentID) values (newid(),'上海一中一班','源数据','47B9AF56-32D8-4561-8096-15D832C47B26')
INSERT INTO TreeShip(ID,Name,Remark,ParentID) values (newid(),'上海一中二班','源数据','47B9AF56-32D8-4561-8096-15D832C47B26')
INSERT INTO TreeShip(ID,Name,Remark,ParentID) values (newid(),'上海一中三班','源数据','47B9AF56-32D8-4561-8096-15D832C47B26')
INSERT INTO TreeShip(ID,Name,Remark,ParentID) values (newid(),'上海二中一班','源数据','64F41586-449A-464B-BB48-FF13FBC492BC')
INSERT INTO TreeShip(ID,Name,Remark,ParentID) values (newid(),'上海二中二班','源数据','64F41586-449A-464B-BB48-FF13FBC492BC')
GO
DECLARE @TreeType nvarchar(20)
SET @TreeType = '新数据'
BEGIN
--创建一个临时表，记录ID的变化
SELECT ID AS FromID,newid() AS ToID INTO #TEMP FROM TreeShip WHERE Remark ='源数据'
--插入相应的数据
INSERT INTO TreeShip(ID,Name,Remark,ParentID)
SELECT M1.ToID,A.Name,@TreeType,ISNULL(M2.ToID,'00000000-0000-0000-0000-000000000000')
FROM TreeShip A
INNER JOIN #TEMP M1 ON A.ID = M1.FromID
LEFT JOIN #TEMP M2 ON A.ParentID = M2.FromID
DROP TABLE #TEMP
END
GO
SELECT * FROM TreeShip ORDER BY REMARK ,PARENTID,NAME
--哥哥们，若是觉得好就给个好评呀，写的很辛苦的。