Is there a set based way to load/read a tree branch with HierarchyId

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

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.

StackExchange Database Administrators Q#17667, answer score: 5