Populando uma tabela com dados hierárquicos existentes

Essa tarefa cria uma tabela nova e a popula com os dados da tabela HumanResources.EmployeeDemo. Essa tarefa tem as seguintes etapas:

• Crie uma tabela que contenha uma coluna hierarchyid. Essa coluna pode substituir as colunas EmployeeID e ManagerID existentes. Entretanto, você manterá essas colunas. Isso porque os aplicativos existentes podem se referir a essas colunas e, também, para ajudar a compreender os dados depois da transferência. A definição da tabela especifica que OrgNode é a chave primária que exige que a coluna contenha valores exclusivos. O índice clusterizado na coluna OrgNode armazenará a data na seqüência OrgNode.

• Crie uma tabela temporária que será usada para localizar quantos funcionários se reportam diretamente a cada gerenciador.

• Popule a tabela nova usando dados da tabela HumanResources.EmployeeDemo.

Para criar uma tabela chamada NewOrg

• Em uma janela do Editor de Consultas, execute o código a seguir para criar uma tabela chamada HumanResources.NewOrg.

  USE AdventureWorks ;
  GO
  
  CREATE TABLE HumanResources.NewOrg
  (
    OrgNode hierarchyid,
    EmployeeID int,
    LoginID nvarchar(50),
    ManagerID int, 
    Title nvarchar(100), 
    HireDate datetime
  CONSTRAINT PK_NewOrg_OrgNode
    PRIMARY KEY CLUSTERED (OrgNode)
  )
  GO
  

Para criar uma tabela temporária chamada #Children

1. Crie uma tabela temporária chamada #Children com uma coluna chamada Num que conterá o número de filhos de cada nó:

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

2. Adicione um índice que acelerará significativamente a consulta que popula a tabela NewOrg:

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

Para popular a tabela NewOrg

1. Consultas recursivas proíbem sub-consultas com agregados. Em vez disso, popule a tabela #Children com o seguinte código que usa o método ROW_NUMBER () para popular a coluna Num:

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

2. Revise a tabela #Children. Observe como a coluna Num contém números seqüenciais para cada gerenciador.

   SELECT * FROM #Children ORDER BY ManagerID, Num
   GO
   

   Conjunto de resultados.

   EmployeeID ManagerID Num
   ---------- --------- ---
   109        NULL      1
   4          3         1
   9          3         2
   11         3         3
   158        3         4
   
   271        6         1
   272        6         2
   

3. Popule a tabela HumanResources.NewOrg. Use métodos GetRoot e ToString para concatenar os valores Num no formato hierarchyid e, depois, atualize a coluna OrgNode com os valores hierárquicos resultantes:

   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 HumanResources.NewOrg (OrgNode, O.EmployeeID, O.LoginID, O.ManagerID, O.Title, O.HireDate)
   SELECT P.path, O.EmployeeID, O.LoginID, O.ManagerID, O.Title, O.HireDate
   FROM HumanResources.EmployeeDemo AS O 
   JOIN Paths AS P 
      ON O.EmployeeID = P.EmployeeID
   GO
   

4. Uma coluna hierarchyid é mais compreensível quando você a converte no formato de caractere. Revise os dados na tabela HumanResources.NewOrg executando o seguinte código que contém duas representações da coluna OrgNode:

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

   A coluna LogicalNode converte a coluna hierarchyid em um formulário de texto mais legível que representa a hierarquia. Nas tarefas restantes, você usará o método ToString() para mostrar o formato lógico das colunas hierarchyid.

5. Descarte a tabela temporária, que não será mais necessária:

   DROP TABLE #Children
   GO
   

A próxima tarefa criará índices para oferecer suporte à estrutura hierárquica.

Próxima tarefa da lição

Otimizando a tabela NewOrg