Click here to Skip to main content
14,384,957 members
Rate this:
Please Sign up or sign in to vote.
See more:
CREATE TABLE dbo.Family
(
Memberid INT NOT NULL,
Hid HIERARCHYID NOT NULL,
Lvl AS hid.GetLevel() PERSISTED,
MemberName VARCHAR(25) NOT
NULL
)

1-How I can insert into the table?
2-how I can set the Hid or deal with it
3-how to use function like GetDescendant to get the benefit from using hierarchical column

Thanks

What I have tried:

It's my first time deal with hierarchical column
Posted
Updated 4-Nov-19 12:51pm

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

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/
   
Comments
Member 14641515 4-Nov-19 18:32pm
   
Thank you for your kindly help
It was very clearly example from yours
MadMyche 4-Nov-19 19:14pm
   
You're welcome

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100