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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy