April 1, 2014 at 2:31 pm
Hi all,
I feel like this should be an easy question to find the answer to, but google searching yielded no useful results, so here goes:
If I have a table with a column that has IDENTITY, when I do an INSERT INTO on that table, it will automatically generate the identity values. In other words, if I do a INSERT INTO Table (Column) SELECT Column FROM Table2, and this query yields 10 results, 10 records will be inserted, with ID numbers being automatically and sequentially generated.
How do I do this for a HierarchyID column? For simplicity's sake, let's say these are the first level entries.
I know that I can write a query such as the following:
INSERT INTO Table (HierarchyIDColumn)
SELECT HierarchyID::GetRoot().GetDescendant(NULL, NULL)
FROM Table2
Problem is that this inserts a set of records which all have the *same* HierarchyID. What I want is for them to be inserted with a sequentially generated HierarchyID instead.
I know I can do this by inserting a row one at a time, and using the GetDescendant with the first parameter being the HierarchyID of the last child to cause it to generate the new HierarchyID next in line, but I feel like it would be odd for this to be the only way ...
April 2, 2014 at 8:47 am
Alright, I guess I found an answer ... doesn't really seem like the intended way of doing it, but this works:
-- Get the current hierarchy number at the level in question
DECLARE @NumChildren INT
SELECT @NumChildren = COUNT(*)
FROM Table
WHERE HierarchyIDColumn.GetAncestor(1) = HierarchyID::GetRoot()
-- Increment using ROW_NUMBER()
INSERT INTO Table (HierarchyIDColumn)
SELECT '/' + CAST(@NumChildren + ROW_NUMBER() OVER (ORDER BY SomeColumn) AS VARCHAR) + '/'
FROM Table2
April 3, 2014 at 11:33 am
So, that last solution works fine when you're talking about a sequential hierarchyID, but if there are elements in the tree missing, or you are dealing with a query where you don't have just one list you're adding, the following ugly query should work:
SELECT
o1.ParentRowPointer.ToString() + CAST(cs.NumElements + ROW_NUMBER() OVER (PARTITION BY o1.ParentRowPointer ORDER BY a.RowPointer) AS VARCHAR) + '/',
FROM ObjectValue o1
CROSS APPLY (
SELECT REPLACE(RIGHT(MAX(o2.ParentRowPointer).ToString(), CHARINDEX('/', REVERSE(MAX(o2.ParentRowPointer).ToString()), 2)), '/', '') AS NumElements
FROM ObjectValue o2
WHERE o2.ParentRowPointer.GetAncestor(1) = o1.ParentRowPointer
) cs
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply