Dear Sir

In real data It goes up to 11 level . I would say not more than 12.

I have tried this but this is wrong anyway. It updates only for top parent not for the sub level.

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