quote:

---

CREATE TABLE Personnel
(emp CHAR(10) PRIMARY KEY,
boss CHAR(10), -- this column is unneeded & denormalizes the
table
salary DECIMAL(6,2) NOT NULL,
lft INTEGER NOT NULL,
rgt INTEGER NOT NULL);

Personnel
emp boss salary lft rgt
===================================
Albert NULL 1000.00 1 12
Bert Albert 900.00 2 3
Chuck Albert 900.00 4 11
Donna Chuck 800.00 5 6
Eddie Chuck 700.00 7 8
Fred Chuck 600.00 9 10

---

       A
      /  \   
    B     C
  /
 D

   (1) A
      /  \   
     B     C
  /
 D

   (1) A
      /   (2) B      C
  /
 D

     (1) A
        /  \   
  (2) B     C
     /
(3) D

     (1) A
        /   \  
   (2) B     C
     /
(3) D (4)

      (1) A
        /   \  
   (2) B (5)  C
     /
(3) D (4)

      (1) A
        /   \    
   (2) B(5) (6)C
     /
(3) D (4)

      (1) A
        /    \   
   (2) B(5) (6)C (7)
     /
(3) D (4)

      (1) A (8)
        /   \    
  (2) B(5) (6)C (7)
     /
(3) D (4)

-- Tree holds the adjacency model
CREATE TABLE Tree
(node CHAR(10) NOT NULL,
 parent CHAR(10));

-- THIS IS BASED ON YOUR ORG DB
INSERT INTO tree(node,parent)
SELDCT OrgID,Parent
FROM ORG

-- Stack starts empty, will holds the nested set model
CREATE TABLE Stack
(stack_top INTEGER NOT NULL,
 node CHAR(10) NOT NULL,
 lft INTEGER,
 rgt INTEGER);

DECLARE @counter INTEGER;
DECLARE @max_counter INTEGER;
DECLARE @current_top INTEGER;

SET @counter = 2;
SET @max_counter = 2 * (SELECT COUNT(*) FROM Tree);
SET @current_top = 1;

--clear the stack
DELETE FROM Stack;

-- push the root
INSERT INTO Stack
SELECT 1, node, 1, @max_counter
FROM Tree
WHERE parent IS NULL;

-- delete rows from tree as they are used
DELETE FROM Tree WHERE parent IS NULL;

WHILE @counter <= @max_counter- 1
BEGIN
 IF EXISTS (SELECT *
              FROM Stack AS S1, Tree AS T1
             WHERE S1.node = T1.parent
               AND S1.stack_top = @current_top)
     
     BEGIN -- push when top has subordinates and set lft value

       INSERT INTO Stack
       SELECT (@current_top + 1), MIN(T1.node), @counter, NULL
         FROM Stack AS S1, Tree AS T1
        WHERE S1.node = T1.parent
          AND S1.stack_top = @current_top;

        -- delete rows from tree as they are used
        DELETE FROM Tree
         WHERE node = (SELECT node
                        FROM Stack
                       WHERE stack_top = @current_top + 1);
        -- housekeeping of stack pointers and counter
        SET @counter = @counter + 1;
        SET @current_top = @current_top + 1;
     END
     ELSE
     BEGIN  -- pop the stack and set rgt value
       UPDATE Stack
          SET rgt = @counter,
              stack_top = -stack_top -- pops the stack
        WHERE stack_top = @current_top
       SET @counter = @counter + 1;
       SET @current_top = @current_top - 1;
     END;
 END 
-- the top column is not needed in the final answer
SELECT node, lft, rgt FROM Stack;

--UPDATE lft/rgt node values in your table
UPDATE Org
SET Org.lft = stack.lft,Org.rgt = stack.rgt
FROM Org INNER JOIN stack ON node = OrgID

DROP TABLE tree
DROP TABLE stack