sql server - TSQL foreach record join a cte and insert -

i need create sql-query uses recursive cte fetch records tablea. (tree-structure). pass him "leaf" , want know way root.

this works @some_id variable

;with cte_recursive          (              select id, sub_id tablea              sub_id = @some_id             union                 select parent.id, parent.sub_id                 tablea parent                 inner join cte_recursive child on child.id = parent.sub_id         )  

what need acchieve is, take every record tableb , use tableb.some_id cte expression , create insert tablec foreach record cte generates plus fields tableb

(cte_recursive.child_id, tableb.somevalue, tableb.someothervalue)

so question here is, how pass tableb.some_id cte expression ?

so in tablea got this:

id, sub_id

1 , 2

2 , 3

2 , 4

2 , 5

5 , 6

7 , 8

8 , 9

if pass him sub_id = 5, cte returns me records #1, #2, #3, #4, #5 sub_id = 5 child of child of child... of id = 1

you can create table valued function

create function ftbranchof (     @some_id int -- actual type of @some_id ) returns table return (     cte_recursive      (          select id, sub_id tablea          sub_id = @some_id         union             select parent.id, parent.sub_id             tablea parent             inner join cte_recursive child on child.id = parent.sub_id     )     select * cte_recursive ) 

and use in query

insert tablec (...) select p.id, b.somevalue, b.someothervalue tableb b     cross apply ftbranchof(b.some_id) p