Использование слияния..вывод, чтобы получить соответствие между исходными.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.TargetID колонка FK.



есть несколько решений для этого.




  1. используйте цикл while или курсоры для вставки одной строки (RBAR) для таргетинга на а время и польза scope_identity() чтобы заполнить FK из TargetChild.

  2. добавить столбец "темп" к @Target и вставить SourceID. Затем вы можете присоединиться к этому столбцу, чтобы получить TargetID для ФК в 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 на выход предложения.




- это префикс столбца, указывающий таблицу, включенную в предложение FROM инструкции DELETE, UPDATE или MERGE, которая используется для указания строк для обновления или удаления.




по какой-то причине они не говорят "строки для вставки, обновления или удаления" только "строки для обновления или исключать."



любые мысли приветствуются, и совершенно разные решения исходной проблемы очень ценятся.

689   2  

2 ответов:

на мой взгляд, это большое использование слияния и вывода. Я использовал в нескольких сценариях и не испытывал никаких странностей на сегодняшний день. Например, вот тестовая установка, которая клонирует папку и все файлы (identity) в ней во вновь созданную папку (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
44  
2013-03-22 10:54:20

Я хотел бы добавить еще один пример, чтобы добавить к примеру @Nathan, так как я нашел его несколько запутанным.

Mine использует реальные таблицы по большей части, а не временные таблицы.

Я также получил свое вдохновение отсюда:еще один пример

-- 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
1  
2017-09-14 14:53:19

Comments

    Ничего не найдено.
Click here to cancel reply.