UPSERT - Is there a better alternative to MERGE or @@rowcount? 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


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"


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"


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
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 ... ) &gt; 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/