I would like to insert the hierarchyId like this
/
- CEO (Root)
/1/
- Purchase Manager
/1/1/
- Purchase Executive/2/
- Sales Manager
/2/1/
- Sales ExecutiveThis 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.
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 hierarchyid
s as per the question.
I do not claim that these are the only ways to insert hierarchyid
s, 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 hierarchyid
s 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.