
use tempdb

create table partstree (
 id int,
 parent int)


insert partstree values(1, null)
insert partstree values(2, null)
insert partstree values(3, 1)
insert partstree values(4, 1)
insert partstree values(5, 3)
select * from partstree


WITH descendant(parent, id, lvl) AS
(SELECT parent, id, lvl =1
  FROM partsTree WHERE id = 1
UNION ALL
SELECT P.parent, P.id, lvl = lvl+1 
 FROM partsTree AS P  INNER JOIN
 descendant AS A ON A.id = P.parent
 where lvl < 2
)
SELECT id, lvl FROM descendant


SELECT parent, id
  into #temp1
  FROM partsTree WHERE id = 1

SELECT P.parent, P.id 
 into #temp2
 FROM partsTree AS P  INNER JOIN
 #temp1 AS A ON A.id = P.parent

SELECT P.parent, P.id 
  into #temp3
 FROM partsTree AS P  INNER JOIN
 #temp2 AS A ON A.id = P.parent

SELECT P.parent, P.id 
  -- into #temp3
 FROM partsTree AS P  INNER JOIN
 #temp3 AS A ON A.id = P.parent

select id from
(
select * from #temp1
union all
select * from #temp2
union all
select * from #temp3
) a

