Copy parent and child rows into same table?

Question

I have a situation similar to this SO question where I want to copy parent rows and their child rows from and into the same table.

The suggested answer using OUTPUT and MERGE is clean but only considers just one record.

Is there a way to modify it to handle multiple parent rows?

EDIT

I tried to modify the SQL statement and came up with this:

DECLARE @fromId int, @toId int;
SET @fromId = 1;
SET @toId = 2;

DECLARE @mapping TABLE (old_id int, new_id int);

INSERT INTO parent_table (col1, col2, col3) 
SELECT @toId, col2, col3
FROM parent_table
WHERE col1 = @fromId;

MERGE child_table tgt
USING (
  SELECT t.parent_id, t.col2, t.col3
  FROM child_table t
  inner join parent_table p on p.id = t.parent_id
  WHERE p.col1 = @toId
) src
ON 0 = 1
WHEN NOT MATCHED THEN
   INSERT (parent_id, col2, col3) VALUES   (src.parent_id, src.col2, src.col3)
OUTPUT src.parent_id, INSERTED.parent_id INTO @mapping (old_id, new_id);

The first INSERT of the parent rows works. However, the second insert does insert any child rows. What am I missing?

Answer 1

You could make use of a UNION operator. We just have to focus on maintaining the column numbers and data types :

 select <required-column names, add null if value for a column not required> from parent_table 
    UNION
    select <required-column with null if value for that column not required> from child_table where id in(select id from parent_table);

For e.g. suppose destination has five columns, parent has three columns, child has two columns. Then, depending on where parent ids should go, we could write :

insert into destination(col1,col2, col3, col4, col5)
select col1, null, col2, col3, null from parent_table where key in(<key values>)
UNION 
select col1, col2, null, null,null from child_table where key in(<key values>);

If all records are to be copied :

insert into destination(col1,col2, col3, col4, col5)
select col1, null, col2, col3, null from parent_table
UNION 
select col1, col2, null, null,null from child_table where key_column in(select key_column from parent_table);