The SQL MERGE statement, introduced in SQL-92, is the best way to update columns in a table if the the row exists, or insert a new row if the row does not exist. This is performed in a single T-SQL statement.
The source code below updates or inserts values into a Prices table that has a Name and Price column.
CREATE PROCEDURE [dbo].[UpsertPrice] ( @name nvarchar(MAX), @price decimal(18, 2) ) AS BEGIN SET NOCOUNT ON; MERGE Prices AS target USING (SELECT @name, @price) AS source (Name, Price) ON (target.Name = source.Name) WHEN MATCHED THEN UPDATE SET target.Price = source.Price WHEN NOT MATCHED THEN INSERT (Name, Price) VALUES (source.Name, source.Price); END;
This combination of update-or-insert values is known by the portmanteau ‘upsert’. I read it on the Internet so it must be true.