Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

OBJECTIVE

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.

CONTEXT

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

OPTIONS

  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

http://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 EXAMPLE

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
share|improve this question

closed as not a real question by JNK Jul 24 '12 at 20:14

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the help center.If this question can be reworded to fit the rules in the help center, please edit the question.

2  
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. –  Aaron Bertrand Jul 24 '12 at 19:31
2  
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 Jul 24 '12 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 Jul 24 '12 at 20:15

1 Answer 1

up vote 5 down vote accepted

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
    UPDATE
ELSE
    INSERT

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.

share|improve this answer
    
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 Jul 24 '12 at 20:02

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