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

Resuelto Mikael Eriksson asked hace 13 años • 3 respuestas

Muy simplificado, tengo dos tablas Fuente y Destino.

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 @Sourcea @Targety conocer TargetIDcada una SourceIDporque también están las tablas SourceChildy TargetChildeso también debe copiarse y necesito agregar lo nuevo TargetIDa TargetChild.TargetIDla columna FK.

Hay un par de soluciones para esto.

  1. Utilice un bucle while o cursores para insertar una fila (RBAR) en Target a la vez y utilícelo scope_identity()para completar el FK de TargetChild.
  2. Agregue una columna temporal @Targete insértela SourceID. Luego puede unirse a esa columna para buscar TargetIDel FK en TargetChild.
  3. SET IDENTITY_INSERT OFFpara @Targety manejar la asignación de nuevos valores usted mismo. Obtienes un rango que luego usas en TargetChild.TargetID.

No soy muy aficionado a ninguno de ellos. El que usé hasta ahora son los cursores.

Lo que realmente me gustaría hacer es utilizar la outputcláusula de la declaración 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? ¿Tiene alguna idea sobre la solución o ve algún problema con ella? Funciona bien en escenarios simples, pero quizás algo feo podría suceder cuando el plan de consulta se vuelva realmente complicado debido a una consulta fuente complicada. El peor escenario sería que los pares TargetID/SourceID en realidad no coincidan.

MSDN tiene esto que decir sobre la from_table_namecláusula de salida .

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

Por alguna razón no dicen "filas para insertar, actualizar o eliminar" sólo "filas para actualizar o eliminar".

Cualquier idea es bienvenida y se agradecen mucho las soluciones totalmente diferentes al problema original.

Mikael Eriksson avatar Mar 20 '11 06:03 Mikael Eriksson
Aceptado

En mi opinión, este es un gran uso de MERGE y salida. Lo he usado en varios escenarios y no he experimentado ninguna rareza hasta la fecha. Por ejemplo, aquí se muestra una configuración de prueba que clona una carpeta y todos los archivos (identidad) que contiene en una carpeta (guid) recién creada.

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
nathan_jr avatar Mar 20 '2011 04:03 nathan_jr

Me gustaría agregar otro ejemplo al de @Nathan, ya que lo encontré algo confuso.

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

También me inspiré en 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
Dragos Durlut avatar Sep 14 '2017 11:09 Dragos Durlut