Usando merge..output para obtener mapeo entre source.id y target.id

Muy simplificado, tengo dos tablas Source y Target.

declare @Source table (SourceID int identity(1,2), SourceName varchar(50)) declare @Target table (TargetID int identity(2,2), TargetName varchar(50)) insert into @Source values ('Row 1'), ('Row 2') 

Me gustaría mover todas las filas de @Source a @Target y conocer el TargetID para cada SourceID porque también están las tablas SourceChild y TargetChild que también deben copiarse y necesito agregar el nuevo TargetID en TargetChild.TargetID FK column .

Hay un par de soluciones para esto.

  1. Use un ciclo while o cursores para insertar una fila (RBAR) en Target a la vez y use scope_identity() para completar el FK de TargetChild .
  2. Agregue una columna temporal a @Target e inserte SourceID . A continuación, puede unirse a esa columna para obtener el TargetID de TargetID para el FK en TargetChild .
  3. SET IDENTITY_INSERT OFF para @Target y manejar la asignación de nuevos valores usted mismo. TargetChild.TargetID un rango que luego usas en TargetChild.TargetID .

No soy muy aficionado a ninguno de ellos. El que utilicé hasta ahora es cursores.

Lo que realmente me gustaría hacer es usar la cláusula de output de la statement de inserción.

 insert into @Target(TargetName) output inserted.TargetID, S.SourceID select SourceName from @Source as S 

Pero no es posible

 The multi-part identifier "S.SourceID" could not be bound. 

Pero es posible con una fusión.

 merge @Target as T using @Source as S on 0=1 when not matched then insert (TargetName) values (SourceName) output inserted.TargetID, S.SourceID; 

Resultado

 TargetID SourceID ----------- ----------- 2 1 4 3 

Quiero saber si has usado esto? Si tiene alguna idea sobre la solución o ve algún problema con ella? Funciona bien en escenarios simples, pero tal vez algo feo podría suceder cuando el plan de consulta se vuelve realmente complicado debido a una consulta fuente complicada. El peor escenario sería que los pares TargetID / SourceID en realidad no coinciden.

MSDN tiene esto que decir sobre el from_table_name de la cláusula de salida .

Es un prefijo de columna que especifica una tabla incluida en la cláusula FROM de una statement DELETE, UPDATE o MERGE que se utiliza para especificar las filas para actualizar o eliminar.

Por alguna razón, no dicen “filas para insertar, actualizar o eliminar” solo “filas para actualizar o eliminar”.

Cualquier idea es bienvenida y las soluciones totalmente diferentes al problema original son muy apreciadas.

En mi opinión, este es un gran uso de MERGE y salida. He utilizado en varios escenarios y no he experimentado ninguna rareza hasta la fecha. Por ejemplo, aquí está la configuración de prueba que clona una Carpeta y todos los Archivos (identidad) dentro de ella en una Carpeta recién creada (GUID).

 DECLARE @FolderIndex TABLE (FolderId UNIQUEIDENTIFIER PRIMARY KEY, FolderName varchar(25)); INSERT INTO @FolderIndex (FolderId, FolderName) VALUES(newid(), 'OriginalFolder'); DECLARE @FileIndex TABLE (FileId int identity(1,1) PRIMARY KEY, FileName varchar(10)); INSERT INTO @FileIndex (FileName) VALUES('test.txt'); DECLARE @FileFolder TABLE (FolderId UNIQUEIDENTIFIER, FileId int, PRIMARY KEY(FolderId, FileId)); INSERT INTO @FileFolder (FolderId, FileId) SELECT FolderId, FileId FROM @FolderIndex CROSS JOIN @FileIndex; -- just to illustrate DECLARE @sFolder TABLE (FromFolderId UNIQUEIDENTIFIER, ToFolderId UNIQUEIDENTIFIER); DECLARE @sFile TABLE (FromFileId int, ToFileId int); -- copy Folder Structure MERGE @FolderIndex fi USING ( SELECT 1 [Dummy], FolderId, FolderName FROM @FolderIndex [fi] WHERE FolderName = 'OriginalFolder' ) d ON d.Dummy = 0 WHEN NOT MATCHED THEN INSERT (FolderId, FolderName) VALUES (newid(), 'copy_'+FolderName) OUTPUT d.FolderId, INSERTED.FolderId INTO @sFolder (FromFolderId, toFolderId); -- copy File structure MERGE @FileIndex fi USING ( SELECT 1 [Dummy], fi.FileId, fi.[FileName] FROM @FileIndex fi INNER JOIN @FileFolder fm ON fi.FileId = fm.FileId INNER JOIN @FolderIndex fo ON fm.FolderId = fo.FolderId WHERE fo.FolderName = 'OriginalFolder' ) d ON d.Dummy = 0 WHEN NOT MATCHED THEN INSERT ([FileName]) VALUES ([FileName]) OUTPUT d.FileId, INSERTED.FileId INTO @sFile (FromFileId, toFileId); -- link new files to Folders INSERT INTO @FileFolder (FileId, FolderId) SELECT sfi.toFileId, sfo.toFolderId FROM @FileFolder fm INNER JOIN @sFile sfi ON fm.FileId = sfi.FromFileId INNER JOIN @sFolder sfo ON fm.FolderId = sfo.FromFolderId -- return SELECT * FROM @FileIndex fi JOIN @FileFolder ff ON fi.FileId = ff.FileId JOIN @FolderIndex fo ON ff.FolderId = fo.FolderId 

Me gustaría agregar otro ejemplo para agregar al ejemplo de @ Nathan, ya que me pareció un tanto confuso.

El mío usa tablas reales en su mayor parte, y no tablas temporales.

También obtuve mi inspiración de aquí: otro ejemplo

 -- Copy the FormSectionInstance DECLARE @FormSectionInstanceTable TABLE(OldFormSectionInstanceId INT, NewFormSectionInstanceId INT) ;MERGE INTO [dbo].[FormSectionInstance] USING ( SELECT fsi.FormSectionInstanceId [OldFormSectionInstanceId] , @NewFormHeaderId [NewFormHeaderId] , fsi.FormSectionId , fsi.IsClone , @UserId [NewCreatedByUserId] , GETDATE() NewCreatedDate , @UserId [NewUpdatedByUserId] , GETDATE() NewUpdatedDate FROM [dbo].[FormSectionInstance] fsi WHERE fsi.[FormHeaderId] = @FormHeaderId ) tblSource ON 1=0 -- use always false condition WHEN NOT MATCHED THEN INSERT ( [FormHeaderId], FormSectionId, IsClone, CreatedByUserId, CreatedDate, UpdatedByUserId, UpdatedDate) VALUES( [NewFormHeaderId], FormSectionId, IsClone, NewCreatedByUserId, NewCreatedDate, NewUpdatedByUserId, NewUpdatedDate) OUTPUT tblSource.[OldFormSectionInstanceId], INSERTED.FormSectionInstanceId INTO @FormSectionInstanceTable(OldFormSectionInstanceId, NewFormSectionInstanceId); -- Copy the FormDetail INSERT INTO [dbo].[FormDetail] (FormHeaderId, FormFieldId, FormSectionInstanceId, IsOther, Value, CreatedByUserId, CreatedDate, UpdatedByUserId, UpdatedDate) SELECT @NewFormHeaderId, FormFieldId, fsit.NewFormSectionInstanceId, IsOther, Value, @UserId, CreatedDate, @UserId, UpdatedDate FROM [dbo].[FormDetail] fd INNER JOIN @FormSectionInstanceTable fsit ON fsit.OldFormSectionInstanceId = fd.FormSectionInstanceId WHERE [FormHeaderId] = @FormHeaderId