How to add a leaf node for a hierarchyid tree

Adding a leaf node to a hierarchyid tree will be done using .GetDescendant( ) function.
ParentNode.GetDescendant(Child1,Child2) If ParentNode, Child1, and Child2 are not NULL, returns a child of parent greater than Child1 and less than Child2. Check out SQL Server online book link from the reference to see exactly what the New Child node will be in different scenarios.

That being said let see how we could add a leaf node in action:
We will be using the previous table we used in previous blog post to demonstrate for this example. Refer about the example table, how we could get a leaf node by following this link (hierearcyid nodes, find leaf node).

From our querying leaf nodes blog we know that “Able” is a leaf node and we would like to add a node named “Paul” under him or we just learned Able become a father and we would like to add his child to our table.

--Adding a leaf node--
 
DECLARE @Child1 VARCHAR(20),
@Child2 VARCHAR(20),
@ParentNode hierarchyid,
@ParentID BIGINT,
@Name Nvarchar(50),
@Level INT;
 
--Initialize
SET @Name = N'Paul';
SELECT @ParentNode = Node,@ParentID = ID,@Level = [Level]+1
       FROM [Parent Child] WHERE Name = 'Abel';
SELECT @Child1 = CONVERT(VARCHAR(20),@ParentNode)+ '1/';
SELECT @Child2 = CONVERT(VARCHAR(20),@ParentNode) + '2/';

In the above script we have to make sure that the first part of the child nodes has the same description as the parent since Able node when it is converted to string will give us /2/ adding 1/ and 2/ will make it /2/1/ for child1 and /2/2/ for child2.

--test
SELECT '@ParentID' AS Variable, @ParentID AS ParentID;
SELECT '@Name' AS Variable,@Name AS NodeName;
SELECT '@Level' AS Variable,@Level AS [Level];
SELECT '@Child1' AS Variable ,@Child1 AS NewValue;
SELECT '@Child2' AS Variable,@Child2 AS NewValue;
SELECT @ParentNode.GetDescendant(@Child1,@Child2).ToString() AS NewChildNode;

Adding a leaf node using a hierarchyid data type in SQL Server - Step2

The NewChildNode actually gave us some floating number between [1,2] as we discussed in the introduction part. Now that we are sure we have the correct values let us insert them to our table and verify they are really properly inserted.

--insert
INSERT INTO [Parent Child] (ParentID,Name,Node,[Level])
      SELECT @ParentID,
             @Name,
             @ParentNode.GetDescendant(@Child1,@Child2).ToString(),
             @Level;
 
--check if inserted
SELECT ID
      ,ParentID
      , Name
      ,[Level]
      ,Node
      ,Node.ToString() [String VALUE]
FROM [Parent Child]
ORDER BY Node;
GO

Adding a leaf node using a hierarchyid data type in SQL Server - Step3

Our new leaf node is properly added indeed.