How to duplicate self-esteem table rows

Question

Suppose we have a self-charging 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 specific month into another MonthId. You must also update the duplicate parentId, entities and their parents should be in the same month.

as an example, suppose that

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 look 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

newId are the values ​​that are generated by the DBMS.

Note. I use Sql-Server 2012 as a DBMS.

Answer 1

This works great 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:

Answer 2

Entity.Name, Entity.MonthId, SQL:

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;

, , Id.