Share via

  • Artículo

Relleno de una tabla con datos jerárquicos existentes

Esta tarea crea una nueva tabla y la rellena con los datos de la tabla HumanResources.EmployeeDemo. Esta tarea consta de los pasos siguientes:

  • Cree una nueva tabla que contenga una columna hierarchyid. Esta columna podría reemplazar a las columnas IdEmpleado y ManagerID. Sin embargo, usted conservará esas columnas. La razón de ello es porque alguna de las aplicaciones existentes podría hacer referencia a esas columnas, y también para ayudarle a entender los datos una vez realizada la transferencia. La definición de tabla especifica que OrgNode es la clave principal, lo cual requiere que la columna contenga valores únicos. El índice clúster sobre la columna OrgNode almacenará la fecha en secuencia OrgNode.

  • Cree una tabla temporal que se utilizará para averiguar cuántos empleados notifican directamente a cada gerente.

  • Rellene la nueva tabla utilizando los datos de la tabla HumanResources.EmployeeDemo.

Para crear una nueva tabla denominada NewOrg

  • En una ventana del editor de consultas, ejecute el siguiente código para crear una nueva tabla llamada 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 crear una tabla temporal denominada #Elementos secundarios

  1. Cree una tabla temporal denominada #Elementos secundarios con una columna denominada Num que contendrá el número de elementos secundarios de cada nodo:

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

  2. Agregue un índice que acelerará significativamente la consulta que rellena la tabla NewOrg:

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

Para rellenar la tabla NewOrg

  1. Las consultas recursivas prohíben las subconsultas con agregados. En su lugar, rellene la tabla #Elementos secundarios con el código siguiente, que utiliza el método ROW_NUMBER () para dar valores a la columna Num:

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

  2. Examine la tabla #Elementos secundarios. Observe cómo la columna Num contiene números secuenciales para cada gerente.

    SELECT * FROM #Children ORDER BY ManagerID, Num
GO

    Éste es el 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. Rellene la tabla HumanResources.NewOrg. Utilice los métodos GetRoot y ToString para concatenar los valores Num en formato hierarchyid y, a continuación, actualice la columna OrgNode con los valores jerá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. Una columna hierarchyid se entiende mejor al convertirla en una cadena de caracteres. Examine los datos en la tabla HumanResources.NewOrg ejecutando el código siguiente, que contiene dos representaciones de la columna OrgNode:

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

    La columna LogicalNode convierte la columna hierarchyid en un texto más fácil de leer que representa la jerarquía. En las tareas restantes, utilizará el método ToString() para mostrar el formato lógico de las columnas hierarchyid.

  5. Elimine la tabla temporal, que ya no se necesita:

    DROP TABLE #Children
GO

La tarea siguiente creará los índices para la estructura jerárquica.

Siguiente tarea de la lección

Optimización de la tabla NewOrg