How to duplicate rows of self-referenced table

Question

Suppose we have a self-referenced table like this

CREATE TABLE Month
(
  Id int IDENTITY(1,1)  PRIMARY KEY,
  Title char(128)
)
CREATE TABLE Entity
(
 Id int IDENTITY(1,1)  PRIMARY KEY,
 MonthId int FOREIGN KEY REFERENCES Month(Id),
 Name char(128),
 ParentId int FOREIGN KEY REFERENCES Entity(Id),
)

I want to copy all rows of a certain MonthId to another MonthId. The duplicate parentId's should be updated as well, the entities and their parents should be in the same month.

as an example assume we have

Id        MonthId    Name     ParentId
------------------------------------
1         1          name1     null
2         1          name11    1
3         1          name3     null 
4         1          name31    3
5         1          name311   4

after copying monthId=1 rows to monthId=2 the result should be like this:

Id        MonthId    Name     ParentId
------------------------------------
1         1          name1     null
2         1          name11    1
3         1          name3     null 
4         1          name31    3
5         1          name311   4
newId1    2          name1     null
newId2    2          name11    newId1
newId3    2          name3     null 
newId4    2          name31    newId3
newId5    2          name311   newId4

The newId's are the values that generated by the DBMS.

Note: I use Sql-Server 2012 as DBMS.

Side note: *please* don't use `char(128)` for your `name` column! This will make **all** your names be 128 characters long - shorter ones padded with spaces up to the defined length! This is horribly bad, waste of space - use `varchar(128)` instead which uses only as much space as needed — marc_s, Jul 12 '15 at 06:39
Its just for the example, I use nvarchar(128) in the real application, as i need Unicode characters. — Taher Rahgooy, Jul 12 '15 at 06:41
I can think of a somewhat cumbersome way of inserting records to a temporary table with the old ids and old parent ids, and then insert them to the main table with the correct values, but I'm sure it can be done in a more straight forward way, so I'm not going to post this as an answer. — Zohar Peled, Jul 12 '15 at 06:52
A. Are you sure your table definition is legal (Specifically, `Month(Id) `)? If so, are you only concerned with the `ParentId` column? — Amit, Jul 12 '15 at 06:53
@Amit, I corrected the schema definition. The main part is the parentId. — Taher Rahgooy, Jul 12 '15 at 07:06

Stepan Novikov · Accepted Answer · 2015-07-12T08:07:19.453

This works fine without any assumptions:

DECLARE @baseMonthId int = 1
DECLARE @newMonthId int = 2

DECLARE @newRows TABLE(id int, orig_id int)

MERGE INTO Entity
USING (
  SELECT Id, Name, ParentId FROM Entity WHERE MonthId = @baseMonthId
) AS cf
ON 1 = 0
WHEN NOT MATCHED THEN
  INSERT(MonthId, Name, ParentId) Values(@newMonthId, cf.Name, cf.ParentId)
OUTPUT inserted.Id, cf.Id INTO @newRows(id, orig_id);

UPDATE Entity
SET Parentid = 
  ( 
    SELECT 
      nr.id
    FROM @newRows nr
      WHERE nr.orig_id = Entity.ParentId
   )
WHERE MonthId = @newMonthId;

Result:

enter image description here

score 0 · Answer 2 · answered Jul 12 '15 at 07:36

If we can rely on Entity.Name being unique for a given Entity.MonthId, then it can be done in 2 SQL statements:

SQLFiddle

-- copy records, but don't set the ParentId yet.
INSERT INTO Entity (MonthId, Name, ParentId)
SELECT 2, Name, null
FROM Entity
WHERE MonthId = 1;

-- set the ParentId in the 2nd step.
UPDATE e
SET e.ParentId = (
  SELECT parentNew.Id
  FROM Entity innerOld
  JOIN Entity parentOld
    ON parentOld.Id = innerOld.ParentId
  JOIN Entity parentNew
    ON parentNew.MonthId = e.MonthId
   AND parentNew.Name = parentOld.Name
  WHERE innerOld.MonthId = 1
    AND innerOld.Name = e.Name
)
FROM Entity e
WHERE e.MonthId = 2;

One advantage of this approach is that it doesn't make any assumptions about how the Id values are generated.

How to duplicate rows of self-referenced table

2 Answers2