title: Get all parents for a child
body: <p>I want to retrieve the parentid of an id, if that parentid has a parent again retrieve it, and so on.
  Kind of hierarchy table.</p>
  
  <pre><code>id----parentid
  1-----1
  5-----1
  47894--5
  47897--47894
  </code></pre>
  
  <p>am new to sql server and tried, some queries like:</p>
  
  <pre><code>with name_tree as 
  (
     select id, parentid
     from Users
     where id = 47897 -- this is the starting point you want in your recursion
     union all
     select c.id, c.parentid
     from users c
     join name_tree p on p.id = c.parentid  -- this is the recursion
  ) 
  select *
  from name_tree;
  </code></pre>
  
  <p>It is giving me only one row.
  and also I want to insert these records into a temporary table variable.
  How can I do this. thanks in advance. sorry for asking the simple question(though not to me)</p>