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:

UPDATE Table
SET columnOne = 'valueOne', columnTwo = 'valueTwo',
WHERE Identifier = 123456
	IF @@ROWCOUNT = 0
		INSERT INTO Table
		(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......

  • custom essay writing service

    6/8/2017 5:30:30 AM |

    Good news! really... I wish you a good luck with your site and if you ever wanted to write interesting essays just feel free to ask here.

Loading