Comment dupliquer des lignes de table auto-référencée

Supposons que nous ayons une table auto-référencée comme celle-ci

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

Je veux copyr toutes les lignes d'un certain MonthId à un autre MonthId. Le parentId en double doit également être mis à jour, les entités et leurs parents doivent être dans le même mois.

à titre d'exemple, supposons que nous avons

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

après avoir copié monthId = 1 lignes à monthId = 2 le résultat devrait être comme ceci:

 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

Les newId sont les valeurs générées par le SGBD.

Note: J'utilise Sql-Server 2012 comme SGBD.

Cela fonctionne bien sans aucune hypothèse:

 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;

Résultat:

entrez la description de l'image ici

Si nous pouvons countr sur Entity.Name étant unique pour un Entity.MonthId donné, alors cela peut être fait dans 2 instructions 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;

Un avantage de cette approche est qu'elle ne fait aucune hypothèse sur la façon dont les valeurs Id sont générées.