How can I use SQL MERGE to upsert single row based on Id, without a source set

Question

There are a number of questions on the proper use of the MERGE statement in SQL Server. They all use a table/set reference for the merge. Is this table reference necessary?

In my case I have a sproc with two parameters @myId and @myValue

I simply want an UPSERT into MyTable based on the column [MyId]

It seems strange that I would have to create a set with

USING (SELECT @myId AS myId) AS source

to proceed with the MERGE (upsert). Is this the only way?

EDIT: voted to close my own question as exact duplicated... but I think the other question's title made it difficult to find.

Out of curiosity, why do you need merge for this use case? What does it buy you, except that the syntax is very hard to construct (as you've found), it doesn't buy you any more concurrency / race condition protection than old-style upsert (unless you add specific locking hints), and there are multiple unresolved bugs with merge? UPDATE / IF @@ROWCOUNT = 0 / INSERT is much easier to write, maintain, and understand. IMHO. — Aaron Bertrand
– Aaron Bertrand, Commented Apr 17, 2013 at 0:38
(PS that is not just lip service. In this blog post, I highlight 12 still unresolved MERGE bugs, and in this blog post, Dan Guzman shows how MERGE on its own does not protect you from race conditions. — Aaron Bertrand
– Aaron Bertrand, Commented Apr 17, 2013 at 0:39
@AaronBertrand Perhaps I will have to research this more, but my understanding was that MERGE in sprocs is faster to process than UPDATE / IF @@ROWCOUNT = 0 / INSERT ... Both links you provide are good, though neither apply to my particular use case. — Matthew
– Matthew, Commented Apr 17, 2013 at 14:31
I'd love to see some performance metrics that back that up. Just because it looks like it should be faster doesn't mean that it is. Deep down in the engine, it has to do the exact same thing that you're doing in slightly more verbose (but certainly more understandable and predictable) ways. — Aaron Bertrand
– Aaron Bertrand, Commented Apr 17, 2013 at 14:33
@AaronBertrand I have to yield to your expertise on the subject, but suffice to say that there are other SQL professionals who have been advocating the MERGE function (perhaps they are not aware of the limitations you've discovered). — Matthew
– Matthew, Commented Apr 17, 2013 at 14:39

muhmud · Accepted Answer · 2013-04-17 07:12:13Z

3

You can also use this syntax:

merge into MyTable mt
using (values (@myId, @myValue)) t(id, value) on mt.Id = t.id
when not matched then insert /* ... */

You're always going to need a set of some kind.

answered Apr 17, 2013 at 7:12

muhmud

4,6242 gold badges20 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How can I use SQL MERGE to upsert single row based on Id, without a source set

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related