Populating the Ragged ORG Bridge table

I have a working script from Ralph that populates the ragged Org Bridge table. It works great, but it was written with Oracle SQL. Unfortunetly it uses the Oracle "connect by prior" clause. Is there anyone out there who could translate this to MS SQL?


/* the Organization table */

Create table DIM_ORG (
ORG_KEY INTEGER NOT NULL,
ORG_NAME VARCHAR2(50),
…….
PARENT_ORG_KEY INTEGER);


/* populate Bridge table */

CREATE or Replace procedure ORG_EXPLOSION_SP as

/* get org-s from org_dimension and put it into Cursor */

CURSOR Get_Roots is
select ORG_KEY as ROOT_KEY,
decode(PARENT_ORG_KEY, NULL,'Y','N') as HIGHEST_FLAG,
ORG_NAME as ROOT_ORG
from ORG;

/* load Bridge table */

BEGIN

For Roots in Get_Roots
LOOP
insert into Bridge_ORG
(PARENT_ORG_KEY,
CHILD_ORG_KEY,
LEVEL,
#_of_levels_from_Parent,
Bottom_Flag,
Top_Flag,
Load_DateTime)
/* */
select
roots.ROOT_KEY,
ORG_KEY,
LEVEL - 1,
ROWNUM,
'N',
roots.HIGHEST_FLAG,
(Select sysdate from dual)
from DIM_ORG
Start with ORG_KEY = roots.ROOT_KEY
connect by prior ORG_KEY = PARENT_KEY;
END LOOP;

update Bridge_ORG
SET Bottom_Flag = 'Y'
where not exists (select * from DIM_ORG
where PARENT_ORG_KEY = BRIDGE_ORG.CHILD_ORG_KEY);
COMMIT;

END;


Thank you
Endre

Just for reference---- Look at the "Populating the Bridge table (Tsql)" discussion. Very nice SQL solution to populate Bridge table, independent from Oracle , MS SQL or DB2