Update or Insert (Upsert) in one query for SQL server

Did you ever wonder if it was possible to do an update or insert in one query? Normally you would write a select query and execute some C# code to see if a certain record already exists and if so do an update and if not do an insert. Besides three SQL query executions this also involves a lot of C# code and doesn't feel like a very efficient approach.

One day I was looking for a better way to solve this very common situation and created this query:

SET columnOne = 'valueOne', columnTwo = 'valueTwo',
WHERE Identifier = 123456
		(columnOne, columnTwo) VALUES ('valueOne', 'valueTwo')

So the query tries to update the record and if the @@ROWCOUNT returns 0, nothing was updated and it will do an insert.

Comments (1) -

  • julia robert

    12/16/2015 1:05:55 PM |

    wow....its really awesome and i fire this command and its really work... two in one query submission is very cool......