Generating HierarchyID

Question

I would like to insert the hierarchyId like this

• / - CEO (Root)
  • /1/ - Purchase Manager
    • /1/1/ - Purchase Executive
  • /2/ - Sales Manager
    • /2/1/ - Sales Executive

This is what the hierarchy i would like to use, is it right one, if so how can i do this, can any one give me some code snippet.

Answer 1

I came across this question while searching for information on the hierarchyid data type, and thought it would be interesting for anyone else coming after me to also see code to insert hierarchyids as per the question.

I do not claim that these are the only ways to insert hierarchyids, but hopefully it will help those who, like me, have no previous experience working with this data type.

Using this table,

create table OrgChart
(
    Position hierarchyid,
    Title nvarchar(50)
)

you can use Parse to directly insert the hierarchyids using the string paths:

insert into OrgChart(Position, Title) 
    values  (hierarchyid::Parse('/'), 'CEO'),
            (hierarchyid::Parse('/1/'), 'Purchase Manager'),
            (hierarchyid::Parse('/1/1/'), 'Purchase Executive'),
            (hierarchyid::Parse('/2/'), 'Sales Manager'),
            (hierarchyid::Parse('/2/1/'), 'Sales Executive')

and use the following query to check the table

select Position.ToString(), * from OrgChart

You can also use the hierarchyid data type methods GetRoot and GetDescendant to build the hierarchy. I found this method to be more cumbersome, but I suppose using these methods is necessary if you are programmatically managing the hierarchy.

declare @root hierarchyid,
        @id hierarchyid

set @root = hierarchyid::GetRoot()

insert into OrgChart(Position, Title) values (@root, 'CEO')

set @id = @root.GetDescendant(null, null)
insert into OrgChart(Position, Title) values (@id, 'Purchase Manager')

set @id = @root.GetDescendant(@id, null)
insert into OrgChart(Position, Title) values (@id, 'Sales Manager')

select @id = Position.GetDescendant(null, null) from OrgChart where Title = 'Purchase Manager'    
insert into OrgChart(Position, Title) values (@id, 'Purchase Executive')

select @id = Position.GetDescendant(null, null) from OrgChart where Title = 'Sales Manager'    
insert into OrgChart(Position, Title) values (@id, 'Sales Executive')

Definitely check out the links provided in the other answer, but hopefully having this code to try out will help as well.