Build Hierarchy table from core table

if(object_id('tempdb..#tmp')>1) drop table #tmp declare @depth int = 0 , @count int = 0 create table #tmp (parent_id int, child_id int, depth_from_parent int) SET @depth = 0; TRUNCATE TABLE #tmp; INSERT INTO #tmp(parent_id, child_id, depth_from_parent) VALUES (1, 1, @depth) WHILE @@ROWCOUNT > 0 BEGIN SET @depth = @depth + 1 INSERT INTO #tmp(parent_id, child_id, depth_from_parent) OUTPUT inserted.child_id, inserted.child_id, 0 INTO #tmp SELECT 1, ou.ou_id, os.depth_from_parent + 1 FROM #tmp os (NOLOCK) INNER JOIN ou (NOLOCK) ou ON os.child_id = ou.parent_id WHERE os.depth_from_parent = @depth - 1; END SET @depth = 0 WHILE @@ROWCOUNT > 0 BEGIN SET @depth = @depth + 1; INSERT INTO #tmp(parent_id, child_id, depth_from_parent) SELECT os.parent_id, ou.ou_id, @depth FROM #tmp os INNER JOIN ou (NOLOCK) ON os.child_id = ou.parent_id --AND os.parent_id <> ou.ou_id -- exclude circular reference WHERE os.depth_from_parent = @depth - 1 AND os.parent_id <> 1 END