Share via

  • Artigo

Populando uma tabela com dados hierárquicos existentes

Essa tarefa cria uma tabela nova e a popula com os dados da tabela 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 sequê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 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.

    CREATE TABLE NewOrg
(
  OrgNode hierarchyid,
  EmployeeID int,
  LoginID nvarchar(50),
  ManagerID int
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

  1. Consultas recursivas proíbem subconsultas 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 EmployeeDemo
GO

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

    SELECT * FROM #Children ORDER BY ManagerID, Num
GO

    Aqui está o conjunto de resultados.

    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

  3. Preencha a tabela 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 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

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

    SELECT OrgNode.ToString() AS LogicalNode, * 
FROM 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