Auffüllen einer Tabelle mit vorhandenen hierarchischen Daten

In dieser Aufgabe wird eine neue Tabelle erstellt und mit den Daten aus der Tabelle HumanResources.EmployeeDemo aufgefüllt. Diese Aufgabe umfasst die folgenden Schritte:

• Erstellen Sie eine neue Tabelle, die eine hierarchyid-Spalte enthält. Diese Spalte könnte die vorhandenen Spalten EmployeeID und ManagerID ersetzen. Sie behalten diese Spalten jedoch bei, weil bestehende Anwendungen möglicherweise auf diese Spalten verweisen und weil dann die Daten nach der Übertragung leichter verständlich sind. Gemäß der Tabellendefinition ist OrgNode der Primärschlüssel, so dass diese Spalte eindeutige Werte enthalten muss. Der gruppierte Index der Spalte OrgNode speichert das Datum in OrgNode-Sequenz.

• Erstellen Sie eine temporäre Tabelle, mit deren Hilfe verfolgt wird, wie viele Mitarbeiter jedem Manager direkt unterstellt sind.

• Füllen Sie die neue Tabelle mit Daten aus der Tabelle HumanResources.EmployeeDemo auf.

So erstellen Sie eine neue Tabelle namens NewOrg

• Führen Sie in einem Abfrage-Editorfenster den folgenden Code aus, um eine einfache Tabelle namens HumanResources.NewOrg zu erstellen.

  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
  

So erstellen Sie eine temporäre Tabelle namens #Children

1. Erstellen Sie eine temporäre Tabelle namens #Children mit einer Spalte namens Num, in der für jeden Knoten die Anzahl der untergeordneten Elemente verzeichnet wird:

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

2. Fügen Sie einen Index hinzu, der die Abfrage, mit der die Tabelle NewOrg aufgefüllt wird, erheblich beschleunigt:

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

So füllen Sie die Tabelle NewOrg auf

1. In rekursiven Abfragen sind Unterabfragen mit Aggregaten nicht zulässig. Füllen Sie die Tabelle #Children stattdessen mit folgendem Code auf, in dem die ROW_NUMBER()-Methode zum Auffüllen der Spalte Num verwendet wird:

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

2. Überprüfen Sie die Tabelle #Children. Die Spalte Num enthält fortlaufende Nummern für die einzelnen Manager.

   SELECT * FROM #Children ORDER BY ManagerID, Num
   GO
   

   Dies ist das Resultset.

   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. Füllen Sie die Tabelle HumanResources.NewOrg auf. Verwenden Sie die Methoden GetRoot und ToString, um die Werte der Spalte Num zu verketten, so dass sie dem hierarchyid-Format entsprechen, und aktualisieren Sie dann die Spalte OrgNode mit den resultierenden hierarchischen Werten:

   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. Eine hierarchyid-Spalte ist verständlicher, wenn sie in das Zeichenformat konvertiert wird. Überprüfen Sie die Daten der Tabelle HumanResources.NewOrg, indem Sie den folgenden Code ausführen, der zwei Darstellungen der Spalte OrgNode enthält:

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

   Die Spalte LogicalNode konvertiert die hierarchyid-Spalte in ein lesbareres Textformat, das die Hierarchie darstellt. In den restlichen Aufgaben werden Sie die ToString()-Methode verwenden, um die hierarchyid-Spalten im logischen Format anzuzeigen.

5. Löschen Sie die temporäre Tabelle, die nicht mehr benötigt wird:

   DROP TABLE #Children
   GO
   

In der nächsten Aufgabe werden Indizes erstellt, um die hierarchische Struktur zu unterstützen.

Nächste Aufgabe in dieser Lektion

Optimieren der NewOrg-Tabelle