Thursday, September 22, 2011
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
Monday, September 19, 2011
Monday, September 12, 2011
Subscribe to:
Posts (Atom)