Monday, October 24, 2011
Thursday, October 20, 2011
Wednesday, October 19, 2011
Friday, October 14, 2011
Thursday, October 06, 2011
Wednesday, October 05, 2011
Monday, October 03, 2011
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
Subscribe to:
Posts (Atom)