2

I'm reluctant to use vendor-specific syntax in SQL because I'd rather leave the solutions we develop platform agnostic, including not being tied to a relational database platform because we drank the Kool-Aid and started using extensions that don't always have clear substitutes on competing vendor platforms.

One such statement that I'm considering is MERGE, Microsoft docs here.

Is there a reason to use it? Is there a performance benefit or something other that you all have found to justify its use over standard SQL + an IF or two?

Tahbaza
  • 9,486
  • 2
  • 26
  • 39
  • 2
    "I'm reluctant to use vendor-specific syntax in SQL" - that's for you to decide – Mitch Wheat Apr 24 '11 at 00:19
  • 1
    If there wasn't a reason to use it, you wouldn't be asking the question. I think your answer is found in your question... don't use anything vendor specific if your goal is to retain options that are easily achievable from other vendors. – Mikecito Apr 24 '11 at 00:25
  • 1
    I believe he is asking because he would rather not use MERGE unless there were a performance gain. – Dennis Apr 24 '11 at 00:35
  • 2
    `MERGE` is an ANSI SQL:2003 **standard** command - it's **not** by any means vendor-specific – marc_s Apr 24 '11 at 08:45
  • Here is one reason to use merge. http://stackoverflow.com/questions/5365629/using-merge-output-to-get-mapping-between-source-id-and-target-id – Mikael Eriksson Apr 24 '11 at 17:06

3 Answers3

5

I like using the merge because it keeps everything together and limits the use of temp or staging tables.

I have an application using a database that pulls updated inventory information every 30-minutes from another server.

The easiest (least sophisticated) solution is to delete the existing data then repopulate the entire table. This sucks for several reasons. Some of the biggest are that users can view a report between this delete and insert and get no data, if something goes wrong with the data pull then I've lost all your data, and I am updating 250,000 records when I only need to update 5,000 or so.

A better solution is to use an insert and an update statement (delete is not necessary in my case). But doing this usually requires a temporary table, table parameter, or a staging table. I hate using all three of those and avoid them whenever I can.

The MERGE statement is nice because I can perform the insert and update while only needing to pull the source data once. The syntax looks crazy, but it's not bad at all after you write a few.

As far as the comment about not wanting to use vendor-specific syntax, well I just can't see it that way. I don't see much that is not vendor-specific. Sure, the MERGE is very different from what is in other databases, and it may stand out. However, it's not like anything moves from one platform to another without every single thing absolutely breaking in every significant way that just makes it easier to start from scratch anyways. Well, that's my personal experience at least. Each database type, MS SQL, MySQL, Oracle, DB2, Informix, etc...They all have their own special syntax rules (like SELECT TOP 1 is different in every freaking database it seems), functions, optimization techniques, and so on. It's not like a MERGE here and there is what is going to kill you on those conversions, you'll be dead long before you deal with rewriting the MERGE statements.

K Richard
  • 1,924
  • 2
  • 22
  • 43
  • 1
    I may be mistaken, but I believe MERGE is part of the ANSI standard, though not many vendors support it. The other variations of upsert/replace syntax are not. – TheXenocide Nov 20 '13 at 14:50
1

It seems that you would have to do 2 queries in standard SQL to achieve what MERGE does. So probably that would be slower, and definitely a little harder to write.

But sure, if you don't want to use MERGE don't use it. I have seen enough projects where time was spent on rewriting stored procedures from T-SQL to PL/SQL only because someone told the customer that expensive Oracle is a "more enterprise solution" for their 50 hits/day website.

Martin Konicek
  • 39,126
  • 20
  • 90
  • 98
0

To my understanding it is simply more "convenient", but don't quote me on that.

Dennis
  • 3,962
  • 7
  • 26
  • 44