Populate hierarchyid column in existing parent-children relationship table?

Question

I search for a way to populate an existing parent-children table:

Tabelle: Categories
-------------------
Columns: CategoryID
         ParentID

I upgrade my sql server to 2008 R2. And now i added the new hierarchyid-datatype to optimize the performance.

So i think to write an sql-trigger to reorganize the hierarchyid-column, when a insert/update will call... but i'm not an sql expert, so i hope someone can help me.

Test Query: (i need to append the string on each recursive call)

;WITH CTE (CategoryID, ParentID, HierarchyString) AS (
    SELECT CategoryID, ParentID, HierarchyString = CAST(ParentID AS VARCHAR(8000)) + '/' + CAST(CategoryID AS VARCHAR(8000))
    FROM Categories
    UNION ALL
    SELECT r.CategoryID, r.ParentID, HierarchyString = CAST(p.CategoryID AS VARCHAR(8000)) + '/' + CAST(r.ParentID AS VARCHAR(8000))
    FROM Categories r 
    INNER JOIN CTE p ON r.CategoryID = p.ParentID
)

SELECT * FROM CTE;

score 2 · Answer 1 · answered Mar 11 '11 at 19:55

I've test a lot of queries and now the following query works, but it's definitely not clean/optimized... maybe someone can improve the query.

;WITH CTE (CategoryID, ParentID, Hierarchy) AS (
    SELECT CategoryID, ParentID, Hierarchy
    FROM Categories
    UNION ALL
    SELECT  p.CategoryID, r.ParentID, hierarchyid::Parse(REPLACE((r.Hierarchy.ToString() + p.Hierarchy.ToString()), '//', '/'))
    FROM Categories r
    INNER JOIN CTE p ON r.CategoryID = p.ParentID
)

SELECT CTE.CategoryID, R.ParentID, CTE.Hierarchy.ToString()
FROM CTE
INNER JOIN Categories AS R ON CTE.CategoryID = R.CategoryID
WHERE CTE.Hierarchy.ToString() LIKE '%/' + CAST(CTE.ParentID AS VARCHAR) + '/' + CAST(CTE.CategoryID AS VARCHAR) + '/%' OR CTE.ParentID = 0

Populate hierarchyid column in existing parent-children relationship table?

1 Answers1