Popolamento di una tabella con dati gerarchici esistenti

Questa attività crea una nuova tabella e la popola con i dati della tabella EmployeeDemo. Questa attività prevede i passaggi seguenti:

• Creare una tabella nuova contenente una colonna hierarchyid. Questa colonna ha sostituito le colonne EmployeeID e ManagerID esistenti. Tuttavia, tali colonne verranno mantenute. Questo avviene perché le applicazioni esistenti potrebbero riferirsi a tali colonne e potrebbero aiutare a capire i dati dopo il trasferimento. La definizione della tabella specifica che OrgNode è la chiave primaria che richiede alla colonna di contenere i valori univoci. L'indice cluster della colonna OrgNode archivierà la data in sequenza di OrgNode.

• Creare una tabella temporanea utilizzata per rilevare il numero di dipendenti che riportano direttamente a ogni responsabile.

• Popolare la nuova tabella utilizzando i dati dalla tabella EmployeeDemo.

Per creare una nuova tabella denominata NewOrg

• In una finestra dell'editor di query, eseguire il codice seguente per creare una nuova tabella denominata HumanResources.NewOrg.

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

Per creare una tabella temporanea denominata #Figli

1. Creare una tabella temporanea denominata #Figli con una colonna denominata Num che conterrà il numero di elementi figlio per ogni nodo:

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

2. Aggiungere un indice per accelerare significativamente la query che popola la tabella NewOrg:

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

Per popolare la tabella NewOrg

1. Le query ricorsive impediscono le sottoquery con aggregazioni. Invece, popolare la tabella #Figli con il codice seguente che utilizza il metodo ROW_NUMBER () per popolare la colonna Num:

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

2. Controllare la tabella #Figli. Si noti in che modo la colonna Num contiene i numeri sequenziali per ogni responsabile.

   SELECT * FROM #Children ORDER BY ManagerID, Num
   GO
   

   Set di risultati:

   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. Popolare la tabella NewOrg. Utilizzare i metodi GetRoot e ToString per concatenare i valori Num nel formato hierarchyid, quindi aggiornare la colonna OrgNode con i valori gerarchici risultanti:

   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. Una colonna hierarchyid è più comprensibile quando viene convertita in un formato carattere. Controllare i dati nella tabella NewOrg eseguendo il codice seguente che contiene due rappresentazioni della colonna OrgNode:

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

   La colonna LogicalNode converte la colonna hierarchyid in un form di testo più leggibile che rappresenta la gerarchia. Nelle attività rimanenti, si utilizzerà il metodo ToString() per mostrare il formato logico delle colonne hierarchyid.

5. Eliminare la tabella temporanea che non risulta più essere necessaria:

   DROP TABLE #Children
   GO
   

L'attività successiva creerà indici per supportare la struttura gerarchica.

Attività successiva della lezione

Ottimizzazione della tabella NewOrg