How do I insert a child record using its parent HierarchyID

[Sam]

Hi,

This is my first serious attempt to use HierarchyID datatype in SQL Server
2008. All the examples I've found insert child nodes by using "relative"
methods i.e. insert under a node whose parent is root.

If I know the HierarchyID value of a node, can I not simply insert my new
child under that node by specifying its "parent" value?

--
Thanks,

Sam

[Plamen Ratchev]

Here is examples to demonstrate how to insert nodes at different levels based on the parent value:

CREATE TABLE Hierarchy (
keycol INT NOT NULL PRIMARY KEY,
foo_name VARCHAR(30),
foo_hierarchy HIERARCHYID);

INSERT INTO Hierarchy VALUES(1, 'top level', hierarchyid::GetRoot());

-- get top level hierachy
DECLARE @top_level HIERARCHYID = (SELECT foo_hierarchy
FROM Hierarchy
WHERE foo_name = 'top level');

-- get last node for top level
DECLARE @last_node HIERARCHYID = (SELECT MAX(foo_hierarchy)
FROM Hierarchy
WHERE foo_hierarchy.GetAncestor(1) = @top_level);

-- insert new node after the last node for the top level
INSERT INTO Hierarchy VALUES(2, 'second level', @top_level.GetDescendant(@last_node, NULL));

-- get second level
DECLARE @second_level HIERARCHYID = (SELECT foo_hierarchy
FROM Hierarchy
WHERE foo_name = 'second level');

-- get last node for second level
SET @last_node = (SELECT MAX(foo_hierarchy)
FROM Hierarchy
WHERE foo_hierarchy.GetAncestor(1) = @second_level);

INSERT INTO Hierarchy VALUES(3, 'third level A', @second_level.GetDescendant(@last_node, NULL));

-- get last node for second level
SET @last_node = (SELECT MAX(foo_hierarchy)
FROM Hierarchy
WHERE foo_hierarchy.GetAncestor(1) = @second_level);

INSERT INTO Hierarchy VALUES(4, 'third level B', @second_level.GetDescendant(@last_node, NULL));

SELECT keycol, foo_name, foo_hierarchy,
foo_hierarchy.ToString() AS hierarchy_as_string
FROM Hierarchy;

/*

keycol foo_name foo_hierarchy hierarchy_as_string
------- --------------- -------------- --------------------
1 top level 0x /
2 second level 0x58 /1/
3 third level A 0x5AC0 /1/1/
4 third level B 0x5B40 /1/2/

*/

DROP TABLE Hierarchy;

--
Plamen Ratchev
http://www.SQLStudio.com