Setting up a Table with Hierarchical Data

The first step in converting a table to a hierarchical structure is to create the table to convert. The following procedure creates a table that contains information on an employee's ID, manager ID, and login ID.

To create the EmployeeDemo table

  • In a Query Editor window, run the following code to create a new table named EmployeeDemo.

    CREATE TABLE EmployeeDemo (EmployeeID int, LoginID varchar(200), ManagerID int);
    INSERT INTO EmployeeDemo 
    VALUES (1, 'zarifin', Null), 
           (2, 'tplate', 1),
           (3, 'hjensen', 1),
           (4, 'schai', 2),
           (5, 'elang', 2),
           (6, 'gsmits', 2),
           (7, 'sdavis', 3),
           (8, 'norint', 3),
           (9, 'jwang', 4),
           (10, 'malexander', 4);

To examine the structure and data of the EmployeeDemo table

  • This new EmployeeDemo table represents a typical table in an existing database that you might want to migrate to a new structure. In a Query Editor window, run the following code to show how the table uses a self join to display the employee/manager relationships:

         Mgr.EmployeeID AS MgrID, Mgr.LoginID AS Manager, 
         Emp.EmployeeID AS E_ID, Emp.LoginID AS LoginID 
    FROM EmployeeDemo AS Emp
    LEFT JOIN EmployeeDemo AS Mgr
    ON Emp.ManagerID = Mgr.EmployeeID

    Here is the result set.

    MgrID Manager E_ID LoginID

    NULL NULL 1 zarifin

    1 zarifin 2 tplate

    1 zarifin 3 hjensen

    2 tplate 4 schai

    2 tplate 5 elang

    2 tplate 6 gsmits

    3 hjensen 7 sdavis

    3 hjensen 8 norint

    4 schai 9 jwang

    4 schai 10 malexander

In the next task, we will create a new table with a hierarchyid data type, and move the data into the new table.