Как можно эффективно объединить две иерархии в SQL Server?

Моделирование вашей иерархии таким образом приведет к проблемам. Столбец Hierary_id нарушает первую нормальную форму, и процесс слияния будет склонен к обновлению аномалий, если вы не сериализуете доступ к узким местам.

Вы должны рассмотреть таблицу только с node_id и parent_id, посмотрите, как это упрощает вашу проблему слияния.

node_id   parent_id
1         NULL
2         1
3         2
4         3

node_id   parent_id
1         NULL
3         1
5         2
6         1

Вы бы использовали рекурсивные запросы с этим, и вы можете быть удивлены, насколько эффективными оказываются планы выполнения. Если вам нужен столбец с плоской иерархией, вы, вероятно, можете создать индексированное представление с рекурсивным запросом.

Мы работали над продуктом, который требовал аналогичного решения. После долгих исследований этого и других подходов мы пришли к выводу, что метод иерархии ID не для нас.

Итак, как прямой ответ на ваш вопрос: нет лучшего способа сделать это, используя этот подход.

Взгляните на Модели вложенных наборов и на Модель списка смежности.

Оба они являются гораздо более элегантными и эффективными решениями этой конкретной дизайнерской задачи.

Редактировать: В заключение, на случай, если вы не женаты на SQL - эту проблему можно гораздо лучше решить с помощью нереляционной базы данных. Мы не могли пойти по этому пути, поскольку ни у кого нет достаточного опыта в проектировании нереляционных баз данных, но в случае, если SQL является необязательным, вы можете использовать свой текущий подход гораздо более удобным и эффективным способом, например, в MongoDB.

Вот решение, которое перемещает строки из исходного @S в целевое @T на один уровень за раз. Чтобы немного упростить, я добавил корневой узел, чтобы всегда иметь родителя, который используется при создании нового HierarcyID.

Я никогда не использовал HierarchyID, поэтому могут быть абсолютно более эффективные способы сделать это, но, по крайней мере, это должно быть более эффективно, чем делать это по одной строке за раз.

-- Target table
declare @T table 
(
  hierarchy_id hierarchyid primary key,
  node_id bigint
)

insert into @T values
('/',             0), -- Needed for simplicity
('/1/',           1),
('/1/1/',         2),
('/1/2/',         3),
('/1/3/',         4)

-- Source table
declare @S table 
(
  hierarchy_id hierarchyid primary key,
  node_id bigint
)

insert into @S values
('/',               0),
('/1/',             1),
('/1/1/',           3),
('/1/2/',           5),
('/1/1/1/',         6)

declare @lvl int = 1

-- Move rows from @S to @T for each level
while exists(select *
             from @S
             where hierarchy_id.GetLevel() = @lvl)
begin

  insert into @T
  select T.hierarchy_id.GetDescendant(C.MaxID, null),
         S.node_id
  from (select S1.node_id, S2.node_id as ParentID
        from @S as S1
          inner join @S as S2
            on S1.hierarchy_id.GetAncestor(1) = S2.hierarchy_id
        where S1.hierarchy_id.GetLevel() = @lvl and
              S1.node_id not in (select node_id
                                 from @T)
       ) as S
    inner join @T as T
      on S.ParentID = T.node_id
    outer apply (select max(hierarchy_id) as MaxID
                 from @T as T2
                 where T.hierarchy_id = T2.hierarchy_id.GetAncestor(1)) as C       

    set @lvl = @lvl + 1
end

select *, hierarchy_id.ToString()
from @T
where hierarchy_id <> hierarchyid::GetRoot()  

Результат:

hierarchy_id  node_id  (No column name)
------------  -------  ----------------
0x58          1        /1/
0x5AC0        2        /1/1/
0x5B40        3        /1/2/
0x5B56        6        /1/2/1/
0x5BC0        4        /1/3/
0x5C20        5        /1/4/