Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 sproc. 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 sproc 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?

share|improve this question
2  
You should also have a read of this. mssqltips.com/sqlservertip/3074/… –  Aaron Bertrand Jul 18 at 7:01

2 Answers 2

up vote 2 down vote accepted

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.

share|improve this answer
1  
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) –  tostringtheory Jul 18 at 5:42
    
@tostringtheory You can use backticks to make @variables not act like pings. –  Aaron Bertrand Jul 18 at 6:59

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.