0

In our code base I see a lot of SQL MERGE statements that only perform an UPDATE or only perform an INSERT or only perform a DELETE. I am having a hard time understanding why developers don't just write an insert or an update or a delete command.

What are the pros and cons of using merge statements that only execute one command?

From what I understand, merges are more problematic, if say a table's hash distribution column changes, or if a row gets updated more than once it will throw an exception (maybe a logic problem at that point, but will throw an exception nonetheless).

Example:

MERGE INTO dbo.my_dltd_recs AS dst
USING (SELECT *
       FROM dbo.my_recs
       WHERE dltd_ind = 'Y') src
ON (dst.my_skey = src.my_skey)
WHEN NOT MATCHED
    -- only operation is INSERT, WHY USE MERGE
    THEN INSERT (my_skey,
                 colA,
                 colB)
         VALUES
         (   src.my_skey,
             src.colA,
             src.colB);
zBomb
  • 339
  • 1
  • 17
  • 4
    IMHO thats silly... as you say merge is complex and has issues, so unless an actual merge is required then use the correct statement. – Dale K Oct 26 '22 at 20:07
  • 2
    The only case I know of where a merge is better is when you need to access columns from the source table in an insert statement using the output clause - or something like that where the output clause of the regular statement doesn't provide all the details required. – Dale K Oct 26 '22 at 20:08
  • 3
    Also a +1 for silly, *especially* as there are numerous documented issues and bugs with Merge in SQL Server that have been known about and [documented](https://sqlblog.org/merge) *a lot* for many years. – Stu Oct 26 '22 at 20:16
  • 1
    An example of what @DaleK mentions is here https://stackoverflow.com/a/15282166/14868997. The points you make aren't normally relevant: *"if say a table's hash distribution column changes"* that is only relevant for Azure Synapse where you cannot use `MERGE`. *"or if a row gets updated more than once it will throw an exception"* but presumably the join key is unique, otherwise what's the point? The main issues are: complex to write and understand, implementation has known bugs. – Charlieface Oct 27 '22 at 01:16
  • Ok, so I'm not Crazy! Thanks! @Charlieface, in my case, I disagree with "not normally relevant". We're migrating an Oracle database that was built off merges like this to an Azure DB and a Synapse DW. You said you cannot use them in Synapse, but we use hundreds of merges in our DW, and DW does not enforce unique constraints so you can't presume the join key is unique. While I admit, our code isn't perfect and neither is our data, by loading all the migrated data into the data warehouse and ingesting more data on top of that, we've ran into both of those examples too many times to count. – zBomb Oct 27 '22 at 19:52

0 Answers0