declare @lastcount int
declare @lastcycle int

Select HierarchyData.Name, HierarchyData.MemberName, 0 as [Cycle] INTO #list
FROM HierarchyData

SET @lastcount = @@rowcount
SET @lastcycle = 0

while @lastcount > 0
BEGIN
  INSERT INTO #list
    SELECT Members.Name, Child.MemberName as [MemberName], @lastcycle+1 as [Cycle]
    FROM #list Members
    JOIN HierarchyData Child ON Members.MemberName = Child.Name
    LEFT JOIN #list cycletest ON Members.Name = cycletest.Name AND Child.MemberName = cycletest.Membername
    WHERE Members.Cycle = @lastcycle AND NOT (Members.Name = Child.MemberName) AND cycletest.Name is null

  SET @lastcount = @@rowcount

  SET @lastcycle = @lastcycle + 1
END

create index idx_list on #list(Name)
create index idx_listmem on #list(MemberName)