11

I am playing around with HierarchyId, and I have not figured out a set based way to do the following:

  • insert all subtree at once
  • retrieve all subtree at once

This question is related to my previous one, and I suspect the only way to accomplish these two tasks with HierarchyId is one node or one level at a time. If I am using materialized path, both actions are easily accomplished by a single (and trivial) set based command.

What am I missing?

Edit: I also missed a way to move a subtree, but I learned it from Mikael Eriksson's comment

Community
  • 1
A-K
  • 7,444
  • 3
  • 35
  • 52

2 Answers2

5

The function to use is GetReparentedValue but when using only GetReparentedValue the tree may end up in an "inconsistent" state.

Here is some code provided by Microsoft that takes care of that. Moving subtrees.

I guess that related to this is Enforcing a tree. It uses a calculated column for parent id that does a self join to the PK.

Mikael Eriksson
  • 22,295
  • 5
  • 63
  • 106
3

Retrieving an entire subtree is simple - use the IsDescendentOf method, as per MSDN

DECLARE @Manager hierarchyid
SELECT @Manager = OrgNode FROM HumanResources.EmployeeDemo
  WHERE LoginID = 'adventure-works\dylan0'

SELECT * FROM HumanResources.EmployeeDemo
WHERE OrgNode.IsDescendantOf(@Manager) = 1

Inserting is more complex but your main issue will be with your constraints - you obviously can't insert child objects until their parent has been committed. In this case either iterate and insert in hierarchical order, or turn off constraints and insert.

When inserting a large amount of data - migration, batch or bulk insert etc - I'd turn off the constraint. When inserting operationally I'd iterate, as I've not encountered instances during the running of the system where large amounts of large need to be inserted to the hierarchy.

Kirk Broadhurst
  • 305
  • 2
  • 8