CODEWITHSUNDEEP
sql-servert-sqlhierarchyid
fmi21
fmi21

Reputation: 545

How to to copy hierarchyid subtree

I want to copy a subtree e.g.

enter image description here

into the same table, at the next free most-right spot (in this case at /5/...).

Is there any stored procedure which may help me do this? If not: how would I do it?

I'm relatively new to SQL so any help will be appreciated.

Upvotes: 0

Views: 147

Answers (1)

Ben Thul
Ben Thul

Reputation: 32697

Those values look like hierarchyid values which makes this fairly easy. Here's what I came up with:

declare @foo table (
    h hierarchyid
);

insert into @foo (h)
values
    ('/4/1/'),
    ('/4/1/1/'),
    ('/4/1/1/6/'),
    ('/4/1/1/7/');

select h.ToString(), 
    h.GetReparentedValue('/4/', '/5/').ToString()
from @foo;

The first two statements are just setup (which, incidentally, will make future questions you ask here more likely to be answered if you provide it yourself!). The last bit shows moving the subtree to a new parent.

To avoid having to hard-code the /5/, I came up with this (based on the same setup as above):

declare @root hierarchyid = '/';
select @root.GetDescendant(
        max(h).GetAncestor(
            max(h).GetLevel()-1),
            NULL
        ).ToString()
from @foo;

This is an informed guess as I don't know your actual data. But that should get you a long way there.

Lastly, to actually change your data to reflect the values that I'm selecting, you'll have to run an update statement. I'll leave that to you.

Upvotes: 1

Related Questions