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

У меня есть две таблицы с иерархией полей, одна из которых является промежуточной таблицей с новыми данными, которые необходимо объединить с другой (то есть набор узлов, которые необходимо добавить в главное дерево, некоторые из которых уже могут быть там).

В дополнение к столбцу иерархии, который определяет древовидную структуру (отношения родительский / дочерний). Каждая таблица имеет отдельный столбец, который содержит идентификатор узла, который однозначно идентифицирует каждый узел. То есть способ узнать, является ли узел из промежуточной таблицы уже в основной таблице осуществляется через идентификатор узла, а не через столбцы иерархии.

Обязательно, обработка, которая должна быть выполняется будет выглядеть примерно так:

For each row, RS, in the staging table:
    If there is not already a row with the same Id as RS in the main table:
         Find the parent, PS, of the staging row
         Find the row, PM, in the main table that has the same node ID as PS
         Create a new child, RM of row PM
         Set PM's ID equal to the ID of RS

Важно отметить, что этот подход будет работать только в том случае, если дерево в промежуточной таблице сортируется / просматривается в порядке ширины - это так, чтобы при обнаружении RS гарантировалось, что его родительский PS уже имеет соответствующую строку в основной таблице.

Пока что единственный способ, которым я могу добиться этого на сервере SQL, - это использовать курсор над промежуточной таблицей (которая уже отсортирована) и вызывать хранимую процедуру для каждой строки который, по сути, делает именно то, что описано выше, в комплекте с SELECT MAX () для поиска самого высокого иерархического идентификатора, который уже существует как дочерний элемент PM, так что дочерний элемент может быть добавлен уникальным образом.

Это ужасно неэффективный подход, хотя , и слишком медленно для моих целей. Есть ли способ лучше?

Для справки, это своего рода технико-экономическая проверка, которую я делаю. Мне нужно выяснить, могу ли я быстро выполнить эту операцию внутри SQL Server. Если окажется, что я не могу, мне придется сделать это по-другому, вне базы данных. Слияние деревьев является неотъемлемой частью (на самом деле, в некотором смысле есть ) проблемной области, поэтому другое структурирование данных или более широкий взгляд и попытки каким-либо образом избежать выполнения этой операции в целом не являются option.

Update

По запросу, вот конкретный пример.

Таблицы "staging" и "main" имеют одинаковые два столбца:

   hierarchy_id of type hierarchyid
   node_id of type bigint

Исходное содержимое

main:

 hierarchy_id    node_id
 /1/             1
 /1/1/           2
 /1/2/           3
 /1/3/           4

staging:

 hierarchy_id    node_id
 /1/             1
 /1/1/           3
 /1/2/           5
 /1/1/1/         6

Желаемое содержимое

main:

 hierarchy_id    node_id
 /1/             1
 /1/1/           2
 /1/2/           3
 /1/3/           4
 /1/4/           5
 /1/2/1/         6

Обратите внимание, что узел в промежуточной таблице с hierarchy_id / 1/1 / соответствует узлу с hiearchy_id / 1/2 / в целевой таблице (вот почему node_id важен - не может просто скопировать значения иерархии_ид).Также обратите внимание, что новый узел с node_id 6 добавляется как дочерний по отношению к правильному родительскому узлу, тот, который имеет node_id 3, поэтому важно hierarchy_id - он определяет древовидную структуру (отношения родитель / потомок) для любых новых узлов. Любое решение должно учитывать оба аспекта.