Export (0) Print
Expand All

Reordering Data in a Hierarchical Table Using Hierarchical Methods

Reorganizing a hierarchy is a common maintenance task. In this task, we will use an UPDATE statement with the GetReparentedValue method to first move a single row to a new location in the hierarchy. Then we will move an entire sub-tree to a new location.

The GetReparentedValue method takes two arguments. The first argument describes the part of the hierarchy to be modified. For example, if a hierarchy is /1/4/2/3/ and you want to change the /1/4/ section, the hierarchy becomes /2/1/2/3/, leaving the last two nodes (2/3/) unchanged, you must provide the changing nodes (/1/4/) as the first argument. The second argument provides the new hierarchy level, in our example /2/1/. The two arguments do not have to contain the same number of levels.

To move a single row to a new location in the hierarchy

  1. Currently Wanida reports to Sariya. In this procedure, you move Wanida from her current node /1/1/, so that she reports to Jill. Her new node will become /3/1/ so /1/ is the first argument and /3/ is the second. These correspond to the OrgNode values of Sariya and Jill. Execute the following code to move Wanida from Sariya's organization to Jill's:

    DECLARE @CurrentEmployee hierarchyid , @OldParent hierarchyid, @NewParent hierarchyid
    SELECT @CurrentEmployee = OrgNode FROM HumanResources.EmployeeOrg
      WHERE EmployeeID = 269 ; 
    SELECT @OldParent = OrgNode FROM HumanResources.EmployeeOrg
      WHERE EmployeeID = 46 ; 
    SELECT @NewParent = OrgNode FROM HumanResources.EmployeeOrg
      WHERE EmployeeID = 119 ; 
    
    UPDATE HumanResources.EmployeeOrg
    SET OrgNode = @CurrentEmployee.GetReparentedValue(@OldParent, @NewParent) 
    WHERE OrgNode = @CurrentEmployee ;
    GO
    
  2. Execute the following code to see the result:

    SELECT OrgNode.ToString() AS Text_OrgNode, 
    OrgNode, OrgLevel, EmployeeID, EmpName, Title 
    FROM HumanResources.EmployeeOrg ;
    GO
    

    Wanida is now at node /3/1/.

To reorganize a section of a hierarchy

  1. To demonstrate how to move a larger number of people at the same time, first execute the following code to add an intern reporting to Wanida:

    EXEC AddEmp 269, 291, 'Kevin', 'Marketing Intern'  ;
    GO
    
  2. Now Kevin reports to Wanida, who reports to Jill, who reports to David. That means that Kevin is at level /3/1/1/. To move all of Jill's subordinates to a new manager, we will update all nodes that have /3/ as their OrgNode to a new value. Execute the following code to update Wanida to report to Sariya, but leaving Kevin reporting to Wanida:

    DECLARE @OldParent hierarchyid, @NewParent hierarchyid
    SELECT @OldParent = OrgNode FROM HumanResources.EmployeeOrg
    WHERE EmployeeID = 119 ; -- Jill
    SELECT @NewParent = OrgNode FROM HumanResources.EmployeeOrg
    WHERE EmployeeID = 46 ; -- Sariya
    DECLARE children_cursor CURSOR FOR
    SELECT OrgNode FROM HumanResources.EmployeeOrg
    WHERE OrgNode.GetAncestor(1) = @OldParent;
    DECLARE @ChildId hierarchyid;
    OPEN children_cursor
    FETCH NEXT FROM children_cursor INTO @ChildId;
    WHILE @@FETCH_STATUS = 0
    BEGIN
    START:
        DECLARE @NewId hierarchyid;
        SELECT @NewId = @NewParent.GetDescendant(MAX(OrgNode), NULL)
        FROM HumanResources.EmployeeOrg WHERE OrgNode.GetAncestor(1) = @NewParent;
    
        UPDATE HumanResources.EmployeeOrg
        SET OrgNode = OrgNode.GetReparentedValue(@ChildId, @NewId)
        WHERE OrgNode.IsDescendantOf(@ChildId) = 1;
        IF @@error <> 0 GOTO START -- On error, retry
            FETCH NEXT FROM children_cursor INTO @ChildId;
    END
    CLOSE children_cursor;
    DEALLOCATE children_cursor;
    
    
  3. Execute the following code to see the result:

    SELECT OrgNode.ToString() AS Text_OrgNode, 
    OrgNode, OrgLevel, EmployeeID, EmpName, Title 
    FROM HumanResources.EmployeeOrg ;
    GO
    

Here is the result set.

Text_OrgNode OrgNode OrgLevel EmployeeID EmpName Title
------------ ------- -------- ---------- ------- -----------------
/            Ox      0        6          David   Marketing Manager
/1/          0x58    1        46         Sariya  Marketing Specialist
/1/1/        0x5AC0  2        269        Wanida  Marketing Assistant
/1/1//2      0x5AD0  3        291        Kevin   Marketing Intern
/2/          0x68    1        271        John    Marketing Specialist
/2/1/        0x6AC0  2        272        Mary    Marketing Assistant
/3/          0x78    1        119        Jill    Marketing Specialist

The entire organizational tree that had reported to Jill (both Wanida and Kevin) now reports to Sariya.

For a stored procedure to reorganize a section of a hierarchy, see the "Moving Subtrees" section of Working with hierarchyid Data.

Community Additions

ADD
Show:
© 2014 Microsoft