merge..output을 사용하여 source.id와 target.id 간의 매핑 가져 오기

merge..output을 사용하여 source.id와 target.id 간의 매핑 가져 오기

---

매우 간단하게 소스와 대상 테이블이 두 개 있습니다.

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

나는에서 모든 행을 이동하고자 @Source하는 @Target과 알고 TargetID각을 SourceID테이블도 있기 때문에 SourceChild와 TargetChild요구가 아니라 복사하는 나는 새를 추가 할 필요가 있음 TargetID에 TargetChild.TargetIDFK 열입니다.

이에 대한 몇 가지 해결책이 있습니다.

1. while 루프 또는 커서를 사용하여 한 번에 하나의 행 (RBAR)을 Target에 삽입하고을 사용 scope_identity()하여의 FK를 채 웁니다 TargetChild.
2. 에 임시 열을 추가 @Target삽입 SourceID. 그런 다음 해당 열을 결합 TargetID하여에서 FK에 대한 을 가져올 수 있습니다 TargetChild.
3. SET IDENTITY_INSERT OFF대한 @Target및 핸들은 새 값을 직접 할당. 그런 다음에서 사용하는 범위를 얻습니다 TargetChild.TargetID.

나는 그들 중 어느 것도 좋아하지 않습니다. 지금까지 사용한 것은 커서입니다.

내가 정말로하고 싶은 output것은 insert 문의 절 을 사용하는 것 입니다.

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

하지만 불가능합니다

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

그러나 병합으로 가능합니다.

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

결과

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

이것을 사용했는지 알고 싶습니다. 솔루션에 대한 생각이 있거나 문제가있는 경우? 간단한 시나리오에서는 잘 작동하지만 복잡한 소스 쿼리로 인해 쿼리 계획이 정말 복잡해지면 추악한 일이 발생할 수 있습니다. 최악의 시나리오는 TargetID / SourceID 쌍이 실제로 일치하지 않는 것입니다.

MSDN가에 대한 대답이있다 from_table_name의 출력 절을.

업데이트 또는 삭제할 행을 지정하는 데 사용되는 DELETE, UPDATE 또는 MERGE 문의 FROM 절에 포함 된 테이블을 지정하는 열 접두사입니다.

어떤 이유로 그들은 "삽입, 업데이트 또는 삭제할 행"이라고 말하지 않고 "업데이트 또는 삭제할 행"만 말합니다.

모든 생각을 환영하며 원래 문제에 대한 완전히 다른 해결책을 많이 주시면 감사하겠습니다.

---

제 생각에는 이것은 MERGE와 출력의 훌륭한 사용입니다. 나는 여러 시나리오에서 사용했으며 지금까지 이상한 일을 경험하지 못했습니다. 예를 들어 다음은 폴더와 그 안의 모든 파일 (ID)을 새로 생성 된 폴더 (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

---

@Nathan의 예제에 추가 할 다른 예제를 추가하고 싶습니다. 다소 혼란 스럽습니다.

내 대부분은 임시 테이블이 아닌 실제 테이블을 사용합니다.

또한 여기에서 영감을 얻었습니다. 또 다른 예

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

참고URL : https://stackoverflow.com/questions/5365629/using-merge-output-to-get-mapping-between-source-id-and-target-id