Way to do MERGE with update source when target not match?

Question

I am trying to write a stored procedure that will take new values from a temp table, merge them into an actual table, giving me the ids of the inserted rows, and the rows that already exist matching its values.

Now, from what I am seeing, the MERGE function doesn't appear to support what I am trying to do, unless I am doing it wrong (completely probable, I'm not a SQL guy -- .NET). Anyways, I really just learned about the MERGE this evening, and it seemed like what I should use at first, but it doesn't seem to have the functionality I need, since I can't just get the ID that existed from before, which I need for the rest of a stored procedure. My main focus was to see if I could get this to work, but the other issue is I need to be sure that the code handles potential concurrent calls to a stored procedure that would run through the MERGE. I know I don't have locking in there now, but I was just trying to get it to work at this point. So, what I have....

Setup

DECLARE @Metadata TABLE
(
    MetadataId INT PRIMARY KEY IDENTITY(1,1),
    MetadataTypeId INT,
    MetadataTypeValueId INT
)

INSERT INTO @Metadata 
VALUES
(1,23),(2,32),(2,33),(2,43),(1,24),(3,33),(1,20)

--Original Table Data
SELECT * FROM @Metadata

DECLARE @MergeMetadata TABLE
(
    MetadataId INT,
    MetadataTypeId INT,
    MetadataTypeValueId INT
)

INSERT INTO @MergeMetadata (MetadataTypeId, MetadataTypeValueId)
VALUES
(2,32),(2,35),(3,34),(4,1),(1,23)

--Metadata that needs added and/or ID'd if exists
SELECT * FROM @MergeMetadata

DECLARE @FinalMetadata TABLE
(
    MetadataId INT,
    MetadataTypeId INT,
    MetadataTypeValueId INT
)

INSERT INTO @FinalMetadata (MetadataId, MetadataTypeId, MetadataTypeValueId)
VALUES
(2,2,32),(8,2,35),(9,3,34),(10,4,1),(1,1,23)

--This is the same Type/TypeValue Id's from the table above
--However, they've been ID'd with existing IDs from the original table
--Or inserted and ID'd if they didn't exist
--Order doesn't matter for the result, I just need the IDs
SELECT * FROM @FinalMetadata

Attempt 1

I first tried to get the value via a merge by doing an insert into the result table when matched, and using the output to get the new values:

--Try to Insert the existing target when matched into the final table
MERGE @Metadata AS T
    USING @MergeMetadata AS S
        ON (S.MetadataTypeId = T.MetadataTypeId 
        AND S.MetadataTypeValueId = T.MetadataTypeValueId)
    WHEN MATCHED
        THEN --CANT INSERT WHEN MATCHED
            INSERT INTO (T.MetadataId, S.MetadataTypeId, S.MetadataTypeValueId) INTO @FinalMetadata
    WHEN NOT MATCHED BY TARGET
        THEN 
            INSERT (MetadataTypeId, MetadataTypeValueId) 
            VALUES (S.MetadataTypeId, S.MetadataTypeValueId)
    OUTPUT inserted.MetadataId, 
           inserted.MetadataTypeId, 
           inserted.MetadataValueTypeId,
           INTO @FinalMetadata
        ;

Attempt 2

I then tried to get the original IDs by using OUTPUT - didn't realize it didn't have a placeholder for UPDATED :(

--Try to get the original IDs by OUTPUT
MERGE @Metadata AS T
    USING @MergeMetadata AS S
        ON (S.MetadataTypeId = T.MetadataTypeId 
        AND S.MetadataTypeValueId = T.MetadataTypeValueId)
    WHEN MATCHED
        THEN 
            UPDATE SET (T.MetadataId = T.MetadataId)
    WHEN NOT MATCHED BY TARGET
        THEN 
            INSERT (MetadataTypeId, MetadataTypeValueId) 
            VALUES (S.MetadataTypeId, S.MetadataTypeValueId)
    OUTPUT inserted.MetadataId, 
           updated.MetadataId, --updated isn't a key for OUTPUT 
           inserted.MetadataValueTypeId,
           INTO @FinalMetadata
        ;

Attempt 3

Finally, I attempted to reverse my attack plan, but I couldn't perform an insert the source

--Try to reverse logic and insert into Source when not matched
MERGE @MergeMetadata T
    USING @Metadata S
        ON (S.MetadataTypeId = T.MetadataTypeId 
        AND S.MetadataTypeValueId = T.MetadataTypeValueId)
    WHEN MATCHED
        THEN UPDATE SET T.MetadataId = S.MetadataId
    WHEN NOT MATCHED BY SOURCE
        THEN --can't insert in NOT MATCHED BY SOURCE
            INSERT (MetadataTypeId, MetadataTypeValueId) 
            VALUES (T.MetadataTypeId, T.MetadataTypeValueId) 
    ; 

Sorry for the length, just wanted to show that I did give this some thought and attempt. At this point, should I instead write the SELECT / IF NOT EXISTS / BEGIN INSERT classic logic? From all I was reading of MERGE, it sounded perfect for this situation, however, it doesn't seem to want to give me what I really need.

If I will have to go the route of other logic, could you provide a snippet of some recommended code that would handle concurrency so concurrent executions wouldn't execute the same DOES NOT EXIST/INSERT at the same time?

Answer 1

Updates are tracked by MERGE by providing:

• the $Action value UPDATE;
• the old values in the memory table deleted; and
• the new values in the memory table inserted.

Therefore both in the case where a match is found, and in the case where there is no match by target, the values of the identity column is available in the inserted memory table.

Answer 2

You've done a lot with one night's experience with MERGE statements!

The DELETED column prefix of the OUTPUT clause should contain the values updated in the MERGE UPDATE SET statements, I think you're on the right track with Attempt 2.