Pattern for copying hierarchical data from one table to another?

Question

Imagine a table with three columns: an ID primary key, a nullable ParentID referencing the ID column on that same table, and a TreeID to indicating the records that make up a particular tree. A basic hierarchical structure might look like this. Note that the ordinal values don't necessarily have an effect on a record's position in the hierarchy.

      dbo.TreeNodes             Visual representation:
+----+----------+--------+    --------------------------
| ID | ParentID | TreeID |       Tree 7:     Tree 15:
+----+----------+--------+       1           10
|  1 | NULL     |      7 |       |–3         |–9
|  2 | 4        |      7 |       |–4
|  3 | 1        |      7 |       |–|–2
|  4 | 1        |      7 |       |-5
|  5 | 1        |      7 |
|  9 | 10       |     15 |
| 10 | NULL     |     15 |
+----+----------+--------+

Given another unrelated table with an identical definition (dbo.TreeNodes2, let's say), I've found that copying the data from the first table to the second while maintaining its self-referential hierarchy isn't so straightforward. I get stuck after inserting the new dbo.TreeNodes2 records when it comes time to populate the ParentID values. I would have to have a mapping table of some kind to know which records from the first table map to the new records in the second table. Here's what I've got so far:

DECLARE @TreeID INT = 7;
-- hard code arbitrary TreeID value for the 2nd table for the sake of demo
DECLARE @NewTreeID INT = 42;

CREATE TABLE #InsertedRows
(
    ID INT NOT NULL PRIMARY KEY
);

INSERT INTO dbo.TreeNodes2
(
    ParentID,
    TreeID
)
OUTPUT Inserted.ID INTO #InsertedRows
SELECT
    NULL, -- ParentID can't be added yet because the IDs don't yet exist.
          -- They need to be updated in later.
    @NewTreeID
FROM dbo.TreeNodes AS tn1
WHERE tn1.TreeID = @TreeID;

I could add a non-related OldID column to dbo.TreeNodes2 to copy the original IDs to, which I could then use to create a mapping to join on and populate the new ParentID records, then null out all the OldID values when I'm done, but this seems like bad design. If SQL Server allowed the OUTPUT clause of an INSERT to include columns from a join, then this would be easy, but all of the documentation, as well as my own testing, indicates that only Inserted.* columns, Deleted.* columns, and scalar values/literals can be used in the OUTPUT clause. Nothing from joins.

My pipe dream, non-valid T-SQL that does what I want would look something like this:

CREATE TABLE #InsertedRows
(
    ID INT NOT NULL PRIMARY KEY,
    OldID INT NOT NULL
);

INSERT INTO dbo.TreeNodes2
(
    ParentID,
    TreeID
)
OUTPUT Inserted.ID, tn1.ID INTO #InsertedRows (ID, OldID)
SELECT
    NULL, -- ParentID can't be added yet because the IDs don't yet exist.
          -- They need to be updated in later.
    @NewTreeID
FROM dbo.TreeNodes AS tn1
WHERE tn1.TreeID = @TreeID;

From here, I would have a mapping table I can use to populate the ParentID values in the new table.

Is there a pattern for handing these kinds of cross-table hierarchical inserts without relying on an extra column whose only purpose is to allow the migration, after which it's an irrelevant column?

Answer 1

In this scenario I would not use IDENTITY for primary keys, but switch to sequences.
This way you can reserve sequence numbers to a temporary table, which became your mapping table, something like:

CREATE SEQUENCE TreeNodes2Sequence START WITH 1 INCREMENT BY 1 ; 

CREATE TABLE TreeNodes2 (
    ID int PRIMARY KEY DEFAULT (NEXT VALUE FOR TreeNodes2Sequence),
    ...
)

CREATE TABLE #InsertedRows
(
    ID INT NOT NULL PRIMARY KEY,
    OldID INT NOT NULL
);

INSERT INTO #InsertedRows
(
    Id,
    OldId
)
SELECT
    NEXT VALUE FOR TreeNodes2Sequence,
    tn1.ID
FROM dbo.TreeNodes AS tn1
WHERE tn1.TreeID = @TreeID;

Then use this temp table to insert all values with null parent and then update parents.