MSSQL 数据表中树形结构的复制

IF EXISTS(SELECT * FROM SYSOBJECTS WHERE name = 'xpPullTreeNodeToNode')
DROP PROC xpPullTreeNodeToNode
GO

CREATE PROC xpPullTreeNodeToNode
(
@p_UserName CHAR(5), -- 5位用户代码
@p_UserPw VARCHAR(20), -- 1-10位用户密码
@p_Mkbh CHAR(1), -- 1位模块编号
@p_hsid SMALLINT,

@SocHospitalID INT, --参考医疗机构ID
@TarHospitalID INT, --目标医疗机构ID
@SocNode INT, --要拷贝的节点
@TargetNode INT --要附加的节点
)
AS
IF @SocNode <= 0 OR @TargetNode <= 0
BEGIN
SELECT -1 AS ERRORCODE, '当前拷贝源节点为空或目标节点为空！' AS ERRORMSG
RETURN -1
END

DECLARE @oIsFolder TINYINT = 0
DECLARE @oIsLeaf TINYINT = 0
DECLARE @oParentID INT = 0
DECLARE @oLevelValue SMALLINT = 0
DECLARE @oRootValue TINYINT = 0
DECLARE @oDeptCode VARCHAR(10) = ''
DECLARE @oTemplateClass VARCHAR(2) = ''
DECLARE @oNodeName VARCHAR(50) = ''

SELECT @oIsFolder = IsFolder,@oIsLeaf = IsLeaf,@oParentID = ParentID,@oDeptCode = DeptCode,@oTemplateClass = TemplateClass,
@oRootValue = RootValue,@oLevelValue = LevelValue,@oNodeName = NodeName
FROM emrdb..EMR_TemplateTree A
WHERE A.ID = @TargetNode AND (HospitalID = @TarHospitalID OR HospitalID = 0)
IF @@ROWCOUNT = 0
BEGIN
SELECT -2 AS ERRORCODE, '当前目标节点不存在！' AS ERRORMSG
RETURN -2
END
IF @oIsFolder = 0 OR @oIsLeaf <> 0
BEGIN
SELECT -3 AS ERRORCODE, '当前目标节点属性数据错误！' AS ERRORMSG
RETURN -3
END

CREATE TABLE #T
(
ID INT NOT NULL,
HospitalID SMALLINT NOT NULL, --医院ID
ParentID INT NOT NULL, --父节点
NodeName VARCHAR(100) NOT NULL, --节点名称
TemplateID INT NOT NULL, --模板ID
IsFolder TINYINT NOT NULL, --节点
IsLeaf TINYINT NOT NULL, --叶子
LevelValue TINYINT NOT NULL, --类型：1,2,3分别对应全院，科室，个人
RootValue TINYINT NOT NULL, --该字段用于在挖掘数据构建树时用到，见存储过程xpOverrideCreateTree
--当该字段值为1表示能在该节点追加叶子
TemplateClass VARCHAR(2) NOT NULL, --该字段用于在挖掘数据构建树时用到，见存储过程xpOverrideCreateTree
WhoUsingNode SMALLINT NOT NULL,
DeptCode VARCHAR(10) NOT NULL, --节点科室
HotPointTimes INT NOT NULL, --热度
HotWords VARCHAR(50) NOT NULL, --关键词
ICD10Code VARCHAR(50) NOT NULL, --ICD10
BodyPart VARCHAR(50) NOT NULL, --身体部位
Confirm INT NOT NULL, --审核人
ConfirmTime DATETIME NOT NULL, --审核时间
ComfirmState TINYINT NOT NULL, --申请状态

NewNodeID INT NOT NULL,
NewParentID INT NOT NULL,
NewTemplateID INT NOT NULL,
DealState TINYINT NOT NULL
)

DECLARE @cTemplateID INT
DECLARE @cIsFolder TINYINT = 0
DECLARE @cIsLeaf TINYINT = 0
DECLARE @cParentID INT = 0
DECLARE @cLevelValue SMALLINT = 0
DECLARE @cRootValue TINYINT = 0
DECLARE @cDeptCode VARCHAR(10) = ''
DECLARE @cTemplateClass VARCHAR(2) = ''
DECLARE @cNodeName VARCHAR(50) = ''
SELECT @cIsFolder = IsFolder,@cIsLeaf = IsLeaf,@cParentID = ParentID,@cDeptCode = DeptCode,@cTemplateClass = TemplateClass,
@cRootValue = RootValue,@cLevelValue = LevelValue,@cNodeName = NodeName
FROM #T WHERE ID = @SocNode

--获取源节点的所有节点含自身节点
INSERT INTO #T(ID, HospitalID, ParentID, NodeName, TemplateID, IsFolder, IsLeaf, LevelValue, RootValue, TemplateClass, WhoUsingNode, DeptCode, HotPointTimes, ICD10Code, HotWords, BodyPart, Confirm, ConfirmTime, ComfirmState,
NewNodeID,NewParentID,NewTemplateID,DealState)
SELECT ID, HospitalID, ParentID, NodeName, TemplateID, IsFolder, IsLeaf, LevelValue, RootValue, TemplateClass, WhoUsingNode, DeptCode, HotPointTimes, ICD10Code, HotWords, BodyPart, Confirm, ConfirmTime, ComfirmState,
0,0,0,0
FROM emrdb..EMR_TemplateTree WHERE ID = @SocNode AND (HospitalID = @SocHospitalID OR HospitalID = 0)

--这里比较巧妙，使用 @@ROWCOUNT > 0
WHILE @@ROWCOUNT > 0
BEGIN
INSERT INTO #T( ID, HospitalID, ParentID, NodeName, TemplateID, IsFolder, IsLeaf, LevelValue, RootValue, TemplateClass, WhoUsingNode, DeptCode, HotPointTimes, ICD10Code, HotWords, BodyPart, Confirm, ConfirmTime, ComfirmState,
NewNodeID,NewParentID,NewTemplateID,DealState)
SELECT A.ID, A.HospitalID, A.ParentID, A.NodeName, A.TemplateID, A.IsFolder, A.IsLeaf, A.LevelValue, A.RootValue, A.TemplateClass, A.WhoUsingNode, A.DeptCode, A.HotPointTimes, A.ICD10Code, A.HotWords, A.BodyPart, A.Confirm, A.ConfirmTime, A.ComfirmState,
0,0,0,0
FROM emrdb..EMR_TemplateTree AS A
INNER JOIN #T AS B
ON A.ParentID = B.ID AND NOT EXISTS(SELECT ID FROM #T C WHERE C.ID = A.ID)
WHERE (A.HospitalID = @SocHospitalID OR A.HospitalID = 0)
--ORDER BY ID--,ParentID
--A.ID not in(select ID from #TTT)
END
--必须按照ID排序，否则会跳过节点
SELECT * INTO #TT FROM #T ORDER BY ID
DECLARE @NextParentID INT = 0
----创建目标节点的下级节点替换源节点缓存表的父节点
--INSERT INTO emrdb..EMR_TemplateTree( HospitalID, ParentID, NodeName, TemplateID, IsFolder, IsLeaf, LevelValue, RootValue, TemplateClass, WhoUsingNode, DeptCode, HotPointTimes, ICD10Code, HotWords, BodyPart, Confirm, ConfirmTime, ComfirmState)
--SELECT @TarHospitalID,@TargetNode,@oNodeName,0,0,1,@oLevelValue,0,@oTemplateClass,0,@oDeptCode,0, '', '', '', 0,'1900-01-01 00:00:00', 0
--IF @@ROWCOUNT = 0
--BEGIN
-- SELECT -4 AS ERRORCODE, '创建当前目标节点的子节点失败！' AS ERRORMSG
-- RETURN -4
--END
--SET @NextParentID = @@IDENTITY

DECLARE @oMaxNodeID INT = 0
DECLARE @oNextNode INT = 0
DECLARE @oStep INT = 1
DECLARE @oMaxID INT = 0
DECLARE @oTemplateID INT = 0
DECLARE @oNewTemplateID INT = 0
DECLARE @oNewRecordID INT = 0
SELECT @oMaxID = MAX(ID) FROM #TT

WHILE @oNextNode < @oMaxID
BEGIN
SET @oNewTemplateID = 0
SET @oNewRecordID = 0
SELECT TOP 1 @oNextNode = ID,@oTemplateID = ISNULL(TemplateID,0),@cIsFolder = IsFolder,@cIsLeaf = IsLeaf,@cParentID = ParentID,@cDeptCode = DeptCode,@cTemplateClass = TemplateClass,
@cRootValue = RootValue,@cLevelValue = LevelValue,@cNodeName = NodeName
FROM #TT WHERE ID > @oNextNode

IF @oTemplateID > 0
BEGIN
--复制该模板记录并更新到缓冲表中NewTemplateID中
INSERT INTO emrdb..EMR_Template(HospitalID,TemplateClass,Title,SexLimit,DeptCode,Username,AutoSign,StateFlag,WordSize,Word)
SELECT @TarHospitalID,TemplateClass,Title,SexLimit,@oDeptCode,'',AutoSign,StateFlag,WordSize,Word FROM emrdb..EMR_Template
WHERE TemplateID = @oTemplateID AND HospitalID = @SocHospitalID
SET @oNewTemplateID = @@IDENTITY
END

--复制当前模板数节点到数据库中
INSERT INTO emrdb..EMR_TemplateTree( HospitalID, ParentID, NodeName, TemplateID, IsFolder, IsLeaf, LevelValue, RootValue, TemplateClass, WhoUsingNode, DeptCode, HotPointTimes, ICD10Code, HotWords, BodyPart, Confirm, ConfirmTime, ComfirmState)
SELECT @TarHospitalID,@cParentID,@cNodeName,@oNewTemplateID,@cIsFolder,@cIsLeaf,@cLevelValue,@cRootValue,@cTemplateClass,0,@oDeptCode,0, '', '', '', 0,'1900-01-01 00:00:00', 0
IF @@ROWCOUNT = 0
BEGIN
SELECT -4 AS ERRORCODE, '创建当前目标节点的子节点失败！' AS ERRORMSG
RETURN -4
END
SET @oNewRecordID = @@IDENTITY
----更新复制的模板ID和新的NewNodeID到缓冲表中
UPDATE #TT SET NewTemplateID = @oNewTemplateID,NewNodeID = @oNewRecordID WHERE ID = @oNextNode
END

----首先更新源目标节点的下面子节点
UPDATE #TT SET NewParentID = @TargetNode WHERE ParentID = @SocNode
SELECT * INTO #Buffer FROM #TT
--然后更新所有节点的父节点
UPDATE #TT SET NewParentID = B.NewNodeID FROM #Buffer B WHERE B.ID = #TT.ParentID
--将源节点更新为目标节点的子节点
UPDATE #TT SET NewParentID = @TargetNode WHERE ID = @SocNode
--SELECT * FROM #TT
UPDATE emrdb..EMR_TemplateTree SET ParentID = A.NewParentID FROM #TT A
WHERE emrdb..EMR_TemplateTree.ID = A.NewNodeID

DROP TABLE #T
DROP TABLE #Buffer
DROP TABLE #TT
SELECT 0 AS ERRORCODE, '复制节点树成功！' AS ERRORMSG
RETURN 0
GO