330
Part III: Advanced T-SQL Data Types Querying Techniques
What you see is that the results show everyone who is a descendant of the root /5/
HierarchyID.BusEntID Name Hierarchy OrganizationLevel
-------- ------------ --------- -----------------263 Jean Trenary /5/ 1
264 Stephanie Conroy /5/1/ 2
291 Gustavo Achong /5/1/ 2
265 Ashvini Sharma /5/1/1/ 3
266 Peter Connelly /5/1/2/ 3
267 Karen Berg /5/2/ 2
268 Ramesh Meyyappan /5/3/ 2
269 Dan Bacon /5/4/ 2
270 François Ajenstat /5/5/ 2
271 Dan Wilson /5/6/ 2
272 Janaina Bueno /5/7/ 2Inserting New Nodes
Now that you have an idea of how to query the HierarchyID data type, how do you modify
it? What if you need to insert some nodes? To insert a node you can simply use the
GetDescendant() method, which returns a child node of the parent node.How does the GetDescendant() method help you? The GetDescendant() method takes
two parameters, which can be either NULL or the hierarchyid of a child of the current node.
Because you already have the current node from the fi rst SELECT statement, simply pass
NULL for both parameters informing the GetDescendant() method that you want to
insert this new node as a descendant of that node.DECLARE @Manager hierarchyidSELECT @Manager = OrganizationNode
FROM HumanResources.Employee
WHERE OrganizationNode = '/5/'INSERT HumanResources.Employee (BusinessEntityID, NationalIDNumber,
LoginID, OrganizationNode,
JobTitle, BirthDate, MaritalStatus, Gender, HireDate, SalariedFlag,
VacationHours, SickLeaveHours, CurrentFlag)
VALUES
(291, '8675309', 'adventure-works\Gustav0', @Manager.GetDescendant
(NULL, NULL),
'Rock Star DBA', '09/15/1966', 'M', 'M', '06/15/2001', 1,
99, 20, 1) ;
You can now rerun the query from earlier and see that indeed you have a new record of
Gustavo Achong, who just happens to be a Rock Star DBA, who is now a descendant of the
Information Services Manager.DECLARE @EmpNode HierarchyIDc13.indd 330c13.indd 330 7/30/2012 4:47:04 PM7/30/2012 4:47:04 PM
http://www.it-ebooks.info