Itzik Ben-Gan described here some simple methods how to work with hierarchyId data type.
http://sqlmag.com/t-sql/hierarchyid
He uses stored proc to add child to some root
-- Web Listing 2: Script to Create Stored Procedure usp_AddEmp
IF OBJECT_ID('dbo.usp_AddEmp', 'P') IS NOT NULL
DROP PROC dbo.usp_AddEmp;
GO
CREATE PROC dbo.usp_AddEmp
@empid AS INT,
@mgrid AS INT = NULL,
@empname AS VARCHAR(25),
@salary AS MONEY
AS
DECLARE
@hid AS HIERARCHYID,
@mgr_hid AS HIERARCHYID,
@last_child_hid AS HIERARCHYID;
IF @mgrid IS NULL
SET @hid = HIERARCHYID::GetRoot();
ELSE
BEGIN
SET @mgr_hid = (SELECT hid FROM dbo.Employees WHERE empid = @mgrid);
SET @last_child_hid =
(SELECT MAX(hid) FROM dbo.Employees
WHERE hid.GetAncestor(1) = @mgr_hid);
SET @hid = @mgr_hid.GetDescendant(@last_child_hid, NULL);
END
INSERT INTO dbo.Employees(empid, hid, empname, salary)
VALUES(@empid, @hid, @empname, @salary);
GO
To add any leaf to the tree need to call this proc like this:
EXEC dbo.usp_AddEmp
@empid = 1, @mgrid = NULL, @empname = 'David' , @salary = $10000.00;
EXEC dbo.usp_AddEmp
@empid = 2, @mgrid = 1, @empname = 'Eitan' , @salary = $7000.00;
EXEC dbo.usp_AddEmp
@empid = 3, @mgrid = 1, @empname = 'Ina' , @salary = $7500.00;
EXEC dbo.usp_AddEmp
@empid = 4, @mgrid = 2, @empname = 'Seraph' , @salary = $5000.00;
EXEC dbo.usp_AddEmp
@empid = 5, @mgrid = 2, @empname = 'Jiru' , @salary = $5500.00;
EXEC dbo.usp_AddEmp
@empid = 6, @mgrid = 2, @empname = 'Steve' , @salary = $4500.00;
EXEC dbo.usp_AddEmp
@empid = 7, @mgrid = 3, @empname = 'Aaron' , @salary = $5000.00;
EXEC dbo.usp_AddEmp
@empid = 8, @mgrid = 5, @empname = 'Lilach' , @salary = $3500.00;
EXEC dbo.usp_AddEmp
@empid = 9, @mgrid = 7, @empname = 'Rita' , @salary = $3000.00;
EXEC dbo.usp_AddEmp
@empid = 10, @mgrid = 5, @empname = 'Sean' , @salary = $3000.00;
EXEC dbo.usp_AddEmp
@empid = 11, @mgrid = 7, @empname = 'Gabriel', @salary = $3000.00;
EXEC dbo.usp_AddEmp
@empid = 12, @mgrid = 9, @empname = 'Emilia' , @salary = $2000.00;
EXEC dbo.usp_AddEmp
@empid = 13, @mgrid = 9, @empname = 'Michael', @salary = $2000.00;
EXEC dbo.usp_AddEmp
@empid = 14, @mgrid = 9, @empname = 'Didi' , @salary = $1500.00;
My question is:
I have a table with list of leaves-brothers which I need to add to the parent 14 on the same level
declare @toadd table
(
empid INT,
mgrid INT,
empname VARCHAR(25),
salary MONEY
)
insert into @toadd(empid, mgrid, empname, salary)
select 15, 14, 'Itzik', $0.01
union all
select 16, 14, 'Jack', $0.02
union all
select 17, 14, 'Alex', $0.03
union all
select 18, 14, 'Mary', $0.04
union all
select 19, 14, 'John', $0.05
I can create cursor and call the proc N times. But probably it can be done more effective? Somehow in one query?
You can use the fact that a string of the form '/[int/][…n]' will convert correctly to a HierarchyId. So rather than use GetDescendant to add a subordinate to a manager, take the manager's HierarchyId, call ToString() on it, tack on '{EmpId}/' to it, and cast it back to a HierarchyId (or just store it if the column is already of this type. Specifically:
with emps_and_mgrs as (
select *, m.hid as mhid
from @toadd as t
cross apply (
select hid
from dbo.Employees
where empid= t.mgrid
) as m
)
INSERT INTO dbo.Employees(empid, hid, empname, salary)
select empid, mhid.ToString() + cast(empid as varchar) + '/', empname, salary
from emps_and_mgrs;
This assumes that everyone's hid value is of the form "{manager's hid}/empid/". This includes David in the original example; rather than having a value of GetRoot() (i.e. "/"), he'd have a value of "/1/". Also of note, if you're going to adopt this strategy, I'd also store the manager's empid on row. That way, you can write a recursive cte that uses only the empid and manager's empid to regenerate all the HierarchyIds if something goes sideways.