Click here to Skip to main content
14,978,175 members
Articles / Database Development / SQL Server
Tip/Trick
Posted 18 Nov 2009

Tagged as

Stats

18.4K views
6 bookmarked

TSQL reparent an entire branch with hierarchyid

Rate me:
Please Sign up or sign in to vote.
3.86/5 (4 votes)
18 Nov 2009CPOL
Reparenting a Tree branch is a PITA using the HierarchyID functions. This reparents a branch by reformating the string representation of the HierarchyID. Supports moving the branch to the root node. I use an ID as the primary key so the proc expects the ID's of the node recordsDECLARE @NodeID INT

Reparenting a Tree branch is a PITA using the HierarchyID functions. This reparents a branch by reformating the string representation of the HierarchyID. Supports moving the branch to the root node.

 I use an ID as the primary key so the proc expects the ID's of the node records

SQL
DECLARE 
	@NodeID INT,
	@NewParentID int
	
SET @NodeID = 1074
SET @NewParentID  = 1073 
SQL
DECLARE
	@OldParent VARCHAR(100),
	@NewParent VARCHAR(100),
	@NodeKey	VARCHAR(100)

--Get the existing parent node key to string
SELECT 
	@OldParent = NodeKey.GetAncestor(1).ToString(),
	@NodeKey = NodeKey.ToString()
FROM ReportSetNode
WHERE NodeID = @NodeID

--Get the new parent to string, removing the 
SELECT @NewParent = Nodekey.ToString() FROM ReportSetNode WHERE NodeID = @NewParentID ;

--deal with the root node where NewParent is NULL
IF @NewParent IS NULL SET @NewParent = '/'

--------------Test select ----------------------
--SELECT NodeKey.ToString(),
--	@NewParent + RIGHT(Nodekey.ToString(),LEN(NodeKey.ToString()) - LEN(@OldParent)) Newkey
--FROM vwReportSetNode 
--WHERE NodeKey.IsDescendantOf(@NodeKey) = 1

--Replace the old parent with the new parent string
UPDATE vwReportSetNode SET
	NodeKey = @NewParent + RIGHT(Nodekey.ToString(),LEN(NodeKey.ToString()) - LEN(@OldParent))
WHERE NodeKey.IsDescendantOf(@NodeKey) = 1

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Mycroft Holmes
Retired None
Australia Australia
Started my programming life writing Excel 1.0 macros, God what a long time ago.

Now I'm a dotnet developer, I get to influence direction, play with new toys, build stuff, life is wonderful.

Greatest buzz you can get, walk past a row of desks and see your application running on all of them (and getting paid).

Greatest irritant, pouring 12 months of knowledge and experience into an empty head only to have it leave.

Comments and Discussions

 
GeneralMoving into a branch with children present Pin
ModuleKev20-Jan-10 23:27
MemberModuleKev20-Jan-10 23:27 
GeneralRe: Moving into a branch with children present Pin
Mycroft Holmes21-Jan-10 0:54
professionalMycroft Holmes21-Jan-10 0:54 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.