Mithilfe von merge..output zu bekommen mapping zwischen Quelle.id und target.id

Sehr vereinfacht, ich habe zwei Tabellen Quelle und Ziel.

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

Ich würde gerne alle Zeilen aus @Source zu @Target und kennen die TargetID für jeden SourceID da gibt es auch die Tabellen SourceChild und TargetChild dass kopiert werden muss, und ich muss hinzufügen, das neue TargetID in TargetChild.TargetID FK-Spalte.

Gibt es ein paar Lösungen.

Mit einer while-Schleife oder der Cursor um eine Zeile einfügen (RBAR) zum Ziel in einer Zeit und verwenden Sie scope_identity() zu füllen, die FK von TargetChild.
Fügen Sie ein temp-Spalte @Target und einfügen SourceID. Sie kann dann kommen, dass die Spalte zu Holen, die TargetID für die FK in TargetChild.
SET IDENTITY_INSERT OFF für @Target und Griff zuweisen neuer Werte selbst. Sie bekommen ein Angebot, dass Sie dann den Einsatz in TargetChild.TargetID.

Ich bin nicht gern einer von Ihnen. Die, die ich bisher verwendet wird-Cursor.

Was würde ich wirklich tun möchte, ist die Verwendung der output - Klausel der insert-Anweisung.

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

Aber es ist nicht möglich

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

Aber es ist möglich, mit einem merge.

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

Ergebnis

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

Möchte ich wissen, ob man diese verwendet? Wenn Sie irgendwelche Gedanken über die Lösung oder finden Sie irgendwelche Probleme mit ihm? Es funktioniert gut in einfachen Szenarien, aber vielleicht etwas hässliches passieren könnte, wenn der Abfrage-plan wirklich kompliziert aufgrund einer komplizierten Quell-Abfrage. Schlimmste Szenario wäre, dass TargetID/SourceID Paare eigentlich nicht für ein match.

MSDN hat dies zu sagen über die from_table_name des Ausgabe - Klausel.

Ist ein Spaltenpräfix, das eine Tabelle angibt, enthalten, in der FROM-Klausel einer DELETE -, UPDATE-oder MERGE-Anweisung wird verwendet, um die Zeilen zu aktualisieren oder zu löschen.

Aus irgendeinem Grund Sie nicht sagen, "Zeilen einfügen, aktualisieren oder löschen Sie" nur "den Zeilen zu aktualisieren oder zu löschen".

Irgendwelche Gedanken sind willkommen und völlig unterschiedliche Lösungen für das ursprüngliche problem wird sehr geschätzt.

Der Grund, Sie nicht zu erwähnen 'einfügen' den ist, weil die from_table_name ist ungültig in der insert into - /Ausgabe-Anweisungen, so wird auch die "gelöscht" - Präfix (da keine vorhandenen Daten geändert werden können, über eine insert -)
BTW: Adam Machanic deckt diese Technik hier
Adam Machanic ' s blog post über die Merge-Funktion ist FANTASTISCH! Löste mein problem genauer. Danke Martin Smith für die Buchung. Wünschte, ich könnte mehr geben als nur +1

InformationsquelleAutor Mikael Eriksson | 2011-03-19

Meiner Meinung nach ist dies ein großer nutzen von MERGE-und-Ausgang. Ich habe in mehreren Szenarien und nicht erlebt haben, alle Merkwürdigkeiten zu Datum.
Zum Beispiel, hier ist das test-setup, dass die Klone einen Ordner und alle Dateien (Identität) in einen neu erstellten Ordner (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

Interessant, aber das scheint außerordentlich Komplex zu erreichen, eine einfache Aufgabe, das kopieren einer Zeile und einer Reihe von untergeordneten Zeilen... würde nicht der Cursor, die eigentlich viel einfacher zu verstehen?

InformationsquelleAutor Nathan Skerl

Möchte ich ein weiteres Beispiel hinzufügen @Nathan ' s Beispiel, da fand ich es etwas verwirrend.

Mine verwendet real-Tabellen für die meisten Teil, und nicht temporäre Tabellen.

Ich habe auch meine inspiration von hier: ein weiteres Beispiel

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

InformationsquelleAutor Dragos Durlut

Schreibe einen Kommentar

Du musst angemeldet sein, um einen Kommentar abzugeben.