The easiest way to insert the
HierarchyID
is by using a
slash-path-slash nomencalature... aka the ToString() variant of the binary HierarchyID.
These are the resources I am using for Hierarchies:
H is for… HierarchyID – Jim O'Neil – Technology Evangelist[
^]
Hierarchical Data (SQL Server) - SQL Server | Microsoft Docs[
^]
INSERT #Family (MemberID, hID, MemberName)
VALUES (0, '/0/', 'Grandfather')
, (1, '/0/1/', 'Dad')
, (2, '/0/1/1/', 'Son')
, (3, '/0/1/2/', 'Brother')
, (4, '/0/2/', 'Uncle')
, (5, '/0/2/1/', 'Cousin')
SELECT MemberID, hID, lvl, MemberName, [hID.ToString()] = hid.ToString()
FROM #Family
ORDER BY hID
Will Return this:
MemberID hID lvl MemberName hID.ToString()
-------- ------ --- ---------- --------------
0 0x48 1 Root /0/
1 0x4AC0 2 Dad /0/1/
2 0x4AD6 3 Son /0/1/1/
3 0x4ADA 3 Brother /0/1/2/
4 0x4B40 2 Uncle /0/2/
5 0x4B56 3 Cousin /0/2/1/