How to duplicate rows of self-referenced table

Taher Rahgooy Published at Dev

Taher Rahgooy

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.

Stepan Novikov

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