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.

Overseas

Check these URLs: Using hierarchyid Data Types (Database Engine) Tutorial: Using the hierarchyid Data Type – Marylnmarylou

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.

Mm

@adrift your first INSERT code assumes you know the hierarchy path before hand and hence is not very maintainable in a realworld scenario. You would need to use the hierarchyid methods (the ones you labelled as cumbersome) for that. – Histolysis

Answer 2

Suppose that you have a table schema with a self-join (as shown below) and that the ManagerID of your CEO is NULL.

CREATE TABLE Employee
(
    EmployeeID int NOT NULL IDENTITY(1,1) PRIMARY KEY
    , JobTitle nvarchar(50) NOT NULL
    , FirstName nvarchar(50) NOT NULL
    , LastName nvarchar(50)
    , ManagerID int
) 

ALTER TABLE dbo.Employee ADD CONSTRAINT
    FK_Employee_Employee FOREIGN KEY
    (
    ManagerID
    ) REFERENCES dbo.Employee
    (
    EmployeeID
    ) ON UPDATE  NO ACTION 
     ON DELETE  NO ACTION 

GO

INSERT INTO Employee(JobTitle, FirstName, LastName, ManagerID)
Values ('Executive', 'Supreme', 'Leader', NULL)

INSERT INTO Employee(JobTitle, FirstName, LastName, ManagerID)
Values ('Manger', 'Boss', 'Man', 1)

INSERT INTO Employee(JobTitle, FirstName, LastName, ManagerID)
Values ('Minion', 'Bob', 'Minion', 2)

INSERT INTO Employee(JobTitle, FirstName, LastName, ManagerID)
Values ('Minion', 'Joe', 'Minion', 2)

GO

You can auto-generate an initial set of hierarchyid values using the following recursive CTE:

;WITH EmployeeHierarchy (
    EmployeeHierarchyID
    , EmployeeID
    , JobTitle
    , LastName
    , FirstName
    , ManagerID
    )
AS (
    SELECT HIERARCHYID::GetRoot() AS EmployeeHierarchyID
        , EmployeeID
        , JobTitle
        , LastName
        , FirstName
        , ManagerID
    FROM Employee
    WHERE ManagerID IS NULL

    UNION ALL

    SELECT HIERARCHYID::Parse(Manager.EmployeeHierarchyID.ToString() + (
                CONVERT(VARCHAR(20), ROW_NUMBER() OVER (
                        ORDER BY DirectReport.EmployeeID
                        ))
                ) + '/') AS EmployeeHierarchy
        , DirectReport.EmployeeID
        , DirectReport.JobTitle
        , DirectReport.LastName
        , DirectReport.FirstName
        , DirectReport.ManagerID
    FROM EmployeeHierarchy AS Manager
    INNER JOIN Employee AS DirectReport
        ON Manager.EmployeeID = DirectReport.ManagerID
    )
SELECT  EmployeeHierarchyID
    , EmployeeID
    , JobTitle
    , LastName
    , FirstName
    , ManagerID
INTO #EmployeeHierarchy
FROM EmployeeHierarchy
ORDER BY EmployeeHierarchyID
GO

It then becomes a fairly trivial matter to add a hierarchyid column to the table, add an index on it, and then populate it by joining to the temp table.

ALTER TABLE dbo.Employee ADD
    EmployeeHierarchyID hierarchyid NULL
GO

UPDATE Employee
SET          Employee.EmployeeHierarchyID = #EmployeeHierarchy.EmployeeHierarchyID
FROM     Employee INNER JOIN
                  #EmployeeHierarchy ON Employee.EmployeeID = #EmployeeHierarchy.EmployeeID
GO

SELECT EmployeeHierarchyID.ToString() AS EmployeeHierarchyIDString, EmployeeID, JobTitle, FirstName, LastName, ManagerID, EmployeeHierarchyID
FROM     Employee
GO

However, bear in mind that if you want the hierarchyid data to remain consistent after you add it, there are very specific ways in which it should be maintained.

Chemical

Here is one more example for the Hid generation when you already have Id-ParentId columns codeproject.com/Articles/1192607/… – Gisarme

I'm not sure if it's only me, but there are plenty of syntax errors in the queries above. For example, the 'Employees' table is referenced as 'Employee' in the second query. – Skiba

@Skiba Ugh. No actual syntax errors but way too many mistakes in names, etc. Thanks for pointing it out. I've cleaned it up and verified by running it on an actual test DB. * facepalm * – Chemical

@Chemical No problem. Thanks for fixing it. I tried to update your post myself, but somehow it didn't allow me. Regardless the errors, once I understood what was the intention of the code, I could achieve what I was looking for. – Skiba