Thursday, June 2, 2011

Populating a Table with Existing Hierarchical Data

To create the EmployeeDemo table

CREATE TABLE EmployeeDemo (EmployeeID int, LoginID varchar(200), ManagerID int);
INSERT INTO EmployeeDemo
VALUES (1, 'zarifin', Null),
(2, 'tplate', 1),
(3, 'hjensen', 1),
(4, 'schai', 2),
(5, 'elang', 2),
(6, 'gsmits', 2),
(7, 'sdavis', 3),
(8, 'norint', 3),
(9, 'jwang', 4),
(10, 'malexander', 4);

To examine the structure and data of the EmployeeDemo table
select * from EmployeeDemo

SELECT
Mgr.EmployeeID AS MgrID, Mgr.LoginID AS Manager,
Emp.EmployeeID AS E_ID, Emp.LoginID
FROM EmployeeDemo AS Emp
LEFT JOIN EmployeeDemo AS Mgr
ON Emp.ManagerID = Mgr.EmployeeID
ORDER BY MgrID, E_ID;

To create a new table named NewOrg

CREATE TABLE NewOrg
(
OrgNode hierarchyid,
EmployeeID int,
LoginID nvarchar(50),
ManagerID int
CONSTRAINT PK_NewOrg_OrgNode
PRIMARY KEY CLUSTERED (OrgNode)
);
GO

To create a temporary table named #Children
CREATE TABLE #Children
(
EmployeeID int,
ManagerID int,
Num int
);
GO

CREATE CLUSTERED INDEX tmpind ON #Children(ManagerID, EmployeeID);
GO


To populate the NewOrg table

INSERT #Children (EmployeeID, ManagerID, Num)
SELECT EmployeeID, ManagerID,
ROW_NUMBER() OVER (PARTITION BY ManagerID ORDER BY ManagerID)
FROM EmployeeDemo
GO

SELECT * FROM #Children ORDER BY ManagerID, Num

Populate the NewOrg table. Use the GetRoot and ToString methods to concatenate the Num values into the hierarchyid format, and then update the OrgNode column with the resultant hierarchical values:

WITH paths(path, EmployeeID)
AS (
-- This section provides the value for the root of the hierarchy
SELECT hierarchyid::GetRoot() AS OrgNode, EmployeeID
FROM #Children AS C
WHERE ManagerID IS NULL

UNION ALL
-- This section provides values for all nodes except the root
SELECT
CAST(p.path.ToString() + CAST(C.Num AS varchar(30)) + '/' AS hierarchyid),
C.EmployeeID
FROM #Children AS C
JOIN paths AS p
ON C.ManagerID = P.EmployeeID
)
INSERT NewOrg (OrgNode, O.EmployeeID, O.LoginID, O.ManagerID)
SELECT P.path, O.EmployeeID, O.LoginID, O.ManagerID
FROM EmployeeDemo AS O
JOIN Paths AS P
ON O.EmployeeID = P.EmployeeID
GO

select * from EmployeeDemo
SELECT OrgNode.ToString() AS LogicalNode, *
FROM NewOrg
ORDER BY LogicalNode;

To index the NewOrg table for efficient searches

ALTER TABLE NewOrg
ADD H_level AS OrgNode.GetLevel() ;
CREATE UNIQUE INDEX EmpBFInd
ON NewOrg(H_level, OrgNode) ;

CREATE UNIQUE INDEX EmpIDs_unq ON NewOrg(EmployeeID) ;

SELECT OrgNode.ToString() AS LogicalNode,
OrgNode, H_Level, EmployeeID, LoginID
FROM NewOrg
ORDER BY OrgNode;

SELECT OrgNode.ToString() AS LogicalNode,
OrgNode, H_Level, EmployeeID, LoginID
FROM NewOrg
ORDER BY H_Level, OrgNode;

SELECT OrgNode.ToString() AS LogicalNode,
OrgNode, H_Level, EmployeeID, LoginID
FROM NewOrg
ORDER BY EmployeeID;
GO