Insert or Update with SQL Server (Upsert)

How to write an insert or update query with SQL Server, also called SQL upsert? Here are two different, quick and easy solutions to write an “UPDATE OR INSERT” query.

In other words, avoid the SQL Server error “Cannot insert duplicate key in object”, “The duplicate key value is” because the row in question already exists in the target table. Indeed, inserting the same key will cause an error.

Before starting, to test both solutions in a practical way, run this script to create an example table and insert two rows of data. The solution is a simple mix of an update command and an insert command.

Insert or Update queries with SQL Server to avoid the unique key violation error (SQL Upsert)

In this example, this code for creating the SALES table is used for the INSERT or UPDATE example. And therefore the update of the rows of the target table.

Note: this SQL insert or update inserts data in a very simple table, simply adjust the code to your current project.

After two consecutive inserts for the same month, we got the error message below.

-- Delete it the table if it already exists
IF exists (
	SELECT 1 FROM sys.objects
	WHERE object_id = object_id(N'[dbo].[SALES]') 
		AND type in (N'U')
)
BEGIN DROP TABLE [dbo].[SALES]
END
GO

-- Create the sample T-SQL table with the column MONTH declared as UNIQUE
CREATE TABLE [dbo].[SALES]
(
	[MONTH] nvarchar(20) UNIQUE,
	[AMOUNT] numeric(5)
)
GO

-- Insert two lines of sales data for the January month as example
INSERT INTO dbo.SALES ( MONTH, AMOUNT ) VALUES ( N'January', 1000);
INSERT INTO dbo.SALES ( MONTH, AMOUNT ) VALUES ( N'January', 2000);

The error message that appears looks like this, with an English SQL Server version

Msg 2627, Level 14, State 1, Line 18
Violation of UNIQUE KEY constraint ‘UQ__SALES__03EA00460E2F3BD5’. Cannot insert duplicate key in object ‘dbo.SALES’. The duplicate key value is (January).
The statement has been terminated.

Two SQL Server approaches for the UPSERT script

Indeed there are two ways or managing the SQL Server Upsert query. Very similar but simply in the opposite order.

The first solution to make an UPSERT with Microsoft SQL Server is done in two steps

Firstly, we test if the row to insert exists in the table, using the EXISTS function.

Then, depending on the result, if the row exists then we perform an UPDATE to update the value, and if it does not exist then we launch an INSERT to insert a new row.

In practice we don’t do an INSERT OR UPDATE, but rather an UPDATE OR INSERT.

IF NOT EXISTS(SELECT * FROM dbo.SALES WHERE MONTH = 'January')
BEGIN
   INSERT INTO dbo.SALES (MONTH, AMOUNT) 
   VALUES ( N'January', 2000);
END
ELSE
   BEGIN
      UPDATE dbo.SALES
      SET AMOUNT = 2000
      WHERE MONTH = 'January';
   END
GO

The second solution is to UPDATE the line then count the updated rows before inserting new data

Secondly, start with an UPDATE of the row. Then, only if the number of updated rows is equal to 0 then execute the INSERT statement.

Finally, the latter inserts a new row for the month of January, which is the key.

UPDATE dbo.SALES
   SET AMOUNT = 2000
   WHERE MONTH = 'January';
   
IF @@ROWCOUNT = 0
BEGIN
   INSERT INTO dbo.SALES ( MONTH, AMOUNT ) 
   VALUES ( N'January', 2000);
END;

Finally, this article has presented you two ways to check the existence of data before insertion or after an update.

To go further, check out these T-SQL examples to of manage SQL Server functions.