Popolamento di una tabella con dati gerarchici esistenti
Questa attività consente di creare una nuova tabella e popolarla 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
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
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
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
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
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
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.
Eliminare la tabella temporanea che non risulta più essere necessaria:
DROP TABLE #Children GO
L'attività successiva creerà indici per supportare la struttura gerarchica.