复制新增树状数据_ParentID也需要更新

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

 

--哥哥们，若是觉得好就给个好评呀，写的很辛苦的。

转载于:https://www.cnblogs.com/pnljs/p/3195999.html