sql - 如何复制自引用表的行

假设我们有一个这样的自引用表

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),
)

我想将某个MonthId的所有行复制到另一个MonthId。重复的parentId也应更新，实体及其父母应在同一月份。

例如，假设我们有

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

将monthId = 1行复制到monthId = 2后，结果应如下所示：

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是由DBMS生成的值。

注意：我使用Sql-Server 2012作为DBMS。

最佳答案

无需任何假设即可正常工作：

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;

结果：