I was wondering if you have encountered a T-SQL command similar to the concept of UPSERT? Performing INSERT|UPDATE operations using options (1) or (2) seems overly complex and error prone.


To ensure that the desired record (in this case employee_id 1) is up-to-date WITHOUT having to having to essentially write the same query twice.


  • table name: employee
  • employee id: has a primary key, and identity proerty is set to true


  1. execute a SQL UPDATE... check @@rowcount = 0 and @@error = 0... execute SQL INSERT if required

    • con: you effectively have to write the same query twice, once as an insert, once as an update
    • con: more code = more time typing
    • con: more code = more room for error

https://stackoverflow.com/questions/1106717/how-to-implement-a-conditional-upsert-stored-procedure "Update using @@rowcount"

  1. execute a SQL MERGE
    • con: you effectively have to write the same query twice, once as an insert, once as an update
    • con: more code = more time typing
    • con: more code = more room for error

http://technet.microsoft.com/en-us/library/bb510625.aspx "T-SQL Merge"

  1. execute a SQL UPSERT (feature does not exist)
    • pro: you define the data-to-table relationship once (let SQL Server worry about whether or not it is an INSERT or an UPDATE)
    • pro: less code = faster implementation
    • pro: less code = lower probability


UPSERT employeee (employee_id, employee_number, job_title, first_name, middle_name, surname, modified_at) VALUES (1, '00-124AB37', 'Manager', 'John', 'T', 'Smith', GetDate());

  • if employee_id 1 does not exist: MS SQL executes a INSERT statement
  • if employee_id 1 exists: MS SQL executes and UPDATE statement
  • 4
    This seems like a feature request for Microsoft, not something anyone here can help you solve. The solution Microsoft came up with is MERGE. If that is not flexible/powerful enough for you, then you need a different solution which doesn't exist yet. Commented Jul 24, 2012 at 19:31
  • 3
    In my opinion, MERGE is straightforward, flexible, and it is also part of SQL Standard. The real problem with MERGE and other UPSERT implementations is potential lock escalation or even deadlocks which has nothing to do with syntax.
    – a1ex07
    Commented Jul 24, 2012 at 19:50
  • If you have a question, feel free to ask it. As written this is basically a diatribe about MERGE implementation in SQL Server.
    – JNK
    Commented Jul 24, 2012 at 20:15
  • Good question - is there essentially an UPSERT statement where the server worries about whether it requires an insert or update. I agree, MERGE doesn't meet what you would logically expect of an implementation of "UPSERT". Given MS chose to implement this way, my question would be: what might have been a shortcoming, or impossible to implement, had they attempted to implement the syntax you (and I) would like? Commented Apr 9, 2019 at 6:58

1 Answer 1


I think the simple answer to this is no. MERGE was Microsoft's answer to the more convoluted UPSERT logic. And you didn't even list the worst approach:

IF (SELECT COUNT ... ) > 0

I just threw up in my mouth a little typing that, but it's actually the one I see most often.

In any case, if MERGE is not flexible or powerful enough for you, I suggest you submit a feature request to Microsoft at http://connect.microsoft.com/sql/ and explain, thoroughly, your business case. As long as you stick to real advantages of your proposed syntax over MERGE, you've got my vote. If you hang too much on the "error-prone" part, I'm not as likely to buy in. Why? Because you can fat-finger any statement.

That said, I don't think there's anything anyone here can do for you specifically. You should investigate the potential problems with MERGE:


  • 2
    Thanks Aaron. I looked through the T-SQL documentation on MSDN and couldn't find what I was looking for; just thought I would throw it out there in case I missed something. While the MERGE statement makes sense in certain situations, I can't help but feel that it is a "sledge hammer to pound in a nail" solution for a simple save operation. Perhaps I should take of my programmer hat, and put on my DBA Fedora. Thank you for taking the time to share your thoughts.
    – Pressacco
    Commented Jul 24, 2012 at 20:02

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