Automatically creating HierarchyID in MSSQL 2008

Question

I am creating a table with both a self referencing foreign key and a hierarchyid.

Is there a simpler way to fill the hierarchyid than creating it for every node somehow like the following?

DECLARE @ID INT = 9

UPDATE  dbo.Tree
SET     Path = ( SELECT Path
             FROM   dbo.Tree
             WHERE  ID = 3).GetDescendant(( SELECT  PATH
                                            FROM    dbo.Tree
                                            WHERE   ID = @ID - 1
                                          ), NULL)
WHERE   ID = @ID

In this example the parent node has ID 3 and 8 and 9 are children of 3.

Answer 1

Best solution I could find by now is setting the root node where ParentID = -1 and then executing the following script.

DECLARE c1 CURSOR FOR (SELECT ID, ParentID FROM dbo.Tree WHERE ParentID != -1)

UPDATE dbo.Tree SET Path = NULL WHERE ParentID != -1

OPEN c1

DECLARE @ID INT, @ParentID INT

FETCH NEXT FROM c1 INTO @ID, @ParentID

WHILE @@FETCH_STATUS = 0
BEGIN

    UPDATE dbo.Tree SET Path = (SELECT Path FROM dbo.Tree WHERE ID = @ParentID).GetDescendant(
    (SELECT MAX(PATH) FROM dbo.Tree WHERE ParentID = @ParentID), NULL) WHERE ID = @ID

    FETCH NEXT FROM c1 INTO @ID, @ParentID
END

CLOSE c1
DEALLOCATE c1