Given a parent / child key table, how can we recursively insert a copy of the structure into another table?
<p>I have a recursive CTE which gives me a listing of a set of parent child keys as follows, lets say its in a temp table called [#relationtree]:</p>

<pre><code>Parent | Child
--------------
1      | 3
3      | 5
5      | 6
5      | 9
</code></pre>

<p>I want to create a copy of these relationships into a table with, lets say, the following stucture:</p>

<pre><code>CREATE TABLE [dbo].[Relations]
(
  [Id] int identity(1,1)
  [ParentId] int
)
</code></pre>

<p>How can I insert the above records but recursively obtain the previously inserted identity value to be able to insert that value as the ParentId column for each copy of a child I insert?</p>

<p>I would expect to have at the end of this in [dbo].[Relations] (given our current seed value is, say 50)</p>

<pre><code>Id | ParentId
-------------
... other rows present before this query ...
50 | NULL
51 | 50
52 | 51
53 | 51
</code></pre>

<p>I'm not sure that scope_identity can work in this situation, or that creating a new temp table with a list of new IDs and inserting identity columns manually is the correct approach?</p>

<p>I could write a cursor / loop to do this, but there must be a nice way of doing some recursive select magic!</p>