Sql-server – Merging into a self referential table with identity columns

This is not a single operation, but a loop that will run n number of times where n is the number of levels in your staging hierarchy.

This uses recursive common table expressions to build the hierarchy path for both a given target table (tree) and a given staging table (staging). The first example uses views containing the recursive code, and the second uses inline ctes if views are not an option.

This solution requires that the entire ancestor path be in the staging table (i.e. from the root node up), and that the combination of parentid,description is unique for each table.

The full code is available in the demos, but here is the recursive cte view for the staging table:

create view dbo.staging_path as 
with cte as (
  select id, description, parentid
      , path = convert(varchar(512),description)
      , parent_path = convert(varchar(512),null) 
  from dbo.staging t
  where t.ParentId is null
  union all
  select c.id, c.description, c.parentid
      , path = convert(varchar(512),p.path+'|'+c.description)
      , parent_path = convert(varchar(512),isnull(p.parent_path+'|','')+p.description)
  from dbo.staging c
    inner join cte p
      on c.parentid = p.id
  )
select id, description, parentid, path, parent_path
from cte;

which returns:

+----+-------------+----------+---------------+-------------+
| id | description | parentid |     path      | parent_path |
+----+-------------+----------+---------------+-------------+
|  1 | Foo         | null     | Foo           | null        |
|  2 | Bar         | 1        | Foo|Bar       | Foo         |
|  3 | Fizz        | 1        | Foo|Fizz      | Foo         |
|  4 | Buzz        | 3        | Foo|Fizz|Buzz | Foo|Fizz    |
+----+-------------+----------+---------------+-------------+

dbfiddle.uk with two recursive views

declare @rc int = 1;

while @rc > 0
begin;
  insert into tree (description, parentid)
    select sp.description, parentid=tp.id
    from dbo.staging_path sp
      left join dbo.tree_path tp
        on sp.parent_path = tp.path
    where not exists (
      select 1
      from dbo.tree_path i
      where sp.path = i.path
      )
      and (tp.id is not null or sp.parentid is null);

  set @rc = @@rowcount;
end;

If you can not create views to simplify the code as above, you can use inline recursive ctes like so:

dbfiddle.uk without views

declare @rc int = 1;

while @rc > 0
  begin;
    with tree_path as (
      select id, description, parentid
          , path = convert(varchar(512),description)
      from dbo.tree t
      where t.ParentId is null
      union all
      select c.id, c.description, c.parentid
          , path = convert(varchar(512),p.path+'|'+c.description)
      from dbo.tree c
        inner join tree_path p
          on c.parentid = p.id
    )
    , staging_path as (
      select id, description, parentid
          , path = convert(varchar(512),description)
          , parent_path = convert(varchar(512),null)
      from dbo.staging t
      where t.ParentId is null
      union all
      select c.id, c.description, c.parentid
          , path = convert(varchar(512),p.path+'|'+c.description)
          , parent_path = convert(varchar(512),isnull(p.parent_path+'|','')+p.description)
      from dbo.staging c
        inner join staging_path p
          on c.parentid = p.id
      )
    insert into tree (description, parentid)
    select sp.description, parentid=tp.id
    from staging_path sp
      left join tree_path tp
        on sp.parent_path = tp.path
    where not exists (
      select 1
      from tree_path i
      where sp.path = i.path
      )
      and (tp.id is not null or sp.parentid is null);

    set @rc = @@rowcount;
  end;

Each option results in tree having the following rows (identity was set to start at 41 for this table to differentiate the id from staging):

+----+-------------+----------+
| Id | Description | ParentId |
+----+-------------+----------+
| 41 | Foo         | null     |
| 42 | Bar         | 41       |
| 43 | Fizz        | 41       |
| 44 | Buzz        | 43       |
+----+-------------+----------+