Populating a Hierarchical Table Using Hierarchical Methods in SQL Server 2012

SQL Server 2012 have greate feature to create Hierarchical Table Using Hierarchical Methods.

Creating EmployeeOrg table for implement Hierarchy.

USE AdventureWorks2012 ;

GO

CREATE TABLE HumanResources.EmployeeOrg

(

   OrgNode HIERARCHYID PRIMARY KEY CLUSTERED,

   OrgLevel AS OrgNode.GetLevel(),

   EmployeeID INT UNIQUE NOT NULL,

   EmpName VARCHAR(20) NOT NULL,

   Title VARCHAR(20) NULL

) ;

GO

CREATE UNIQUE INDEX EmployeeOrgNc1

ON HumanResources.EmployeeOrg(OrgLevel, OrgNode) ;

GO

To insert the root of the hierarchy tree:

INSERT HumanResources.EmployeeOrg (OrgNode, EmployeeID, EmpName, Title)

VALUES (HIERARCHYID::GetRoot(), 6, 'Raju', 'CEO') ;

GO

To create a procedure for entering new nodes:

1.      To simplify entering data, create the following stored procedure to add employees to the EmployeeOrg table. The procedure accepts input values about the employee being added. This includes the EmployeeID of the new employee's manager, the new employee's EmployeeID number, and their first name and title. The procedure uses GetDescendant() and also the GetAncestor() method. Execute the following code to create the procedure:

CREATE PROC AddEmp(@mgrid int, @empid int, @e_name varchar(20), @title varchar(20))

AS

BEGIN

   DECLARE @mOrgNode hierarchyid, @lc hierarchyid

   SELECT @mOrgNode = OrgNode

   FROM HumanResources.EmployeeOrg

   WHERE EmployeeID = @mgrid

   SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

   BEGIN TRANSACTION

      SELECT @lc = max(OrgNode)

      FROM HumanResources.EmployeeOrg

      WHERE OrgNode.GetAncestor(1) =@mOrgNode ;

      INSERT HumanResources.EmployeeOrg (OrgNode, EmployeeID, EmpName, Title)

      VALUES(@mOrgNode.GetDescendant(@lc, NULL), @empid, @e_name, @title)

   COMMIT

END ;

GO

2.       The following example adds the remaining 4 employees that report directly or indirectly to David. 

EXEC AddEmp 6, 10, 'Srinivas', 'Marketing Specialist';

EXEC AddEmp 6, 55, 'Rambabu', 'Marketing Expert';

EXEC AddEmp 55, 269, 'Bhanu', 'Marketing Assistant';

EXEC AddEmp 10, 272, 'Kotesh', 'Development Assistant';

3.       Again, execute the following query examine the rows in the EmployeeOrg table

SELECT OrgNode.GetAncestor(1) AS Ancestor, OrgNode.ToString() AS Text_OrgNode, OrgNode, OrgLevel, EmployeeID, EmpName, Title

FROM HumanResources.EmployeeOrg ;

GO

Here is the result set.