기존 계층적 데이터로 테이블 채우기

각 노드의 자식 수를 포함할 Num이라는 열이 포함된 #Children이라는 임시 테이블을 만듭니다.

CREATE TABLE #Children 
   (
    EmployeeID int,
    ManagerID int,
    Num int
);
GO

NewOrg 테이블을 채우는 쿼리의 속도를 상당히 높일 인덱스를 추가합니다.

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

재귀 쿼리는 집계가 있는 하위 쿼리를 금지합니다. 대신 ROW_NUMBER() 메서드를 통해 Num 열을 채우는 다음 코드를 사용하여 #Children 테이블을 채웁니다.

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

#Children 테이블을 검토합니다. Num 열에 각 관리자에 대한 일련 번호가 포함되는 방식을 확인합니다.

SELECT * FROM #Children ORDER BY ManagerID, Num
GO

결과 집합은 다음과 같습니다.

EmployeeID ManagerID Num

---------- --------- ---

1 NULL 1

2 1 1

3 1 2

4 2 1

5 2 2

6 2 3

7 3 1

8 3 2

9 4 1

10 4 2

NewOrg 테이블을 채웁니다. GetRoot 및 ToString 메서드를 사용하여 Num 값을 hierarchyid 형식에 연결한 다음 OrgNode 열을 결과 계층 값으로 업데이트합니다.

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

hierarchyid 열을 문자 형식으로 변환하면 이해하기가 더 쉬워집니다. OrgNode 열의 두 가지 표현이 포함된 다음 코드를 실행하여 NewOrg 테이블의 데이터를 검토합니다.

SELECT OrgNode.ToString() AS LogicalNode, * 
FROM NewOrg 
ORDER BY LogicalNode;
GO

LogicalNode 열은 계층을 나타내는 보다 읽기 쉬운 텍스트 형식으로 hierarchyid 열을 변환합니다. 나머지 태스크에서는 ToString() 메서드를 사용하여 hierarchyid 열의 논리 형식을 표시합니다.

더 이상 필요하지 않은 임시 테이블을 삭제합니다.

DROP TABLE #Children
GO