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?

• Aaron Bertrand: You should also have a read of this. mssqltips.com/sqlservertip/3074/…

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.

• tostringtheory: Holy. Freaking. Cow. I didn't get the fact that the UPDATE was stored and keeping track of per field values. Now I understand why I was seeing some articles use an @Dummy variable and just doing WHEN MATCHED @Dummy = 1 or something, which threw all of the columns in the inserted.* space. I saw it several times, but never saw it mentioned why the hell they did it or what that exact operator was doing. (escaping @ so it doesn't think I'm notifying @Dummy)
• Aaron Bertrand: @tostringtheory You can use backticks to make @variables not act like pings.

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.