MERGE command in SQL

I just recently started using the MERGE command in SQL.  You would use this instead of a IF, Then, Else statement.  It doesn’t make sense to use this in place of If, Then, Else all the time, but in certain circumstances.  My example is repetition in checking, then inserting or updating data, across Development, Stage and Production servers, that for some reason may not be consistent or the same with regards to their data in the same table. 

So… If I have 20 records that need to be inserted or updated, depending if this is the first time running the command or the Nth time, this comes in handy.  The values in the USING statement, can be referred to by variable names for the rest of the MERGE statement, using the Source prefix in both the Join statement and the Insert statement.  Based on the code snippet in the image below, I can code this once, then copy it and only have a minimum amount of changes for the next attribute. 

Although this may be more verbose compared to most If, Then, Else statements, it is very handy when managing large amounts of data across multiple environment that may be out of sync (but that is another compliant for another day)

1 MERGE dbo.SurveyAttributes AS tgt 2 USING (SELECT '5CEFDA70F5A44844A254A646C9EDFC93' AS CtelAccountID, 'UseFlexScoring' AS AttributeType, 651 AS SurveyID ) AS Source 3 ON (tgt.CTelAccountID = Source.CtelAccountID AND tgt.AttributeType = Source.AttributeType AND tgt.SurveyId = Source.SurveyID) -- join condition 4 WHEN MATCHED THEN 5 UPDATE SET AttributeValue = 'TRUE', ChangeDate = GETDATE(), Active = 'Y' 6 WHEN NOT MATCHED THEN 7 INSERT (CtelAccountID, AttributeType, AttributeValue, SurveyID, DateCreated, ChangeUser, ChangeDate, Active) 8 VALUES (Source.CtelAccountId, Source.AttributeType, 'TRUE', Source.SurveyId, GETDATE(), 'GLOBAL\BLACKJR', GETDATE(), 'Y') ; 9

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.