UPSERT - Is there a better alternative to MERGE or @@rowcount? [closed]

Question

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

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

2. 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"

3. 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

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
    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. You should investigate the potential problems with MERGE:

http://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/