To detect the Parent Hierarchy only not applicable to child sub level Hierarchy

drop table #table

go

create table #table (Value varchar(max), [ParentID] int)

go

create index ix_ParentID on #table (ParentID)

go

  

Insert into #table

select   cast([ParentID] as varchar(5)) + '\' +

      cast([childID]  as varchar(5)) value, [ParentID]

from staging.Hierarchy

  

While (@@ROWCOUNT > 0)

Begin

update a

set a.value = cast(b.[ParentID] as varchar(5)) + '\' + a.value,

a.[ParentID] = b.[ParentID]

from #table a inner join staging.hierarchy b on a.[ParentID] = b.[childID]

End

insert into Hierarchy (Value , [ParentID] )

select *  from #table

select * from staging.Hierarchy