Generating HierarchyID

I would like to insert the hierarchyId like this

• / - CEO (Root)
  • /1/ - Purchase Ma开发者_StackOverflow中文版nager
    • /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.

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.

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.