How to write an insert or update SQL Server query? 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.
How to manage the SQL Server Insert or Update queries to avoid unique key violation?
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.
-- If the table already exists, then we delete it 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 -- Creation of the example table with the column MONTH declared as UNIQUE CREATE TABLE [dbo].[SALES] ( [MONTH] nvarchar(20) UNIQUE, [AMOUNT] numeric(5) ) GO -- Insert data for the 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.
The two SQL Server approaches are “INSERT OR UPDATE” and “UPDATE OR INSERT”
The first solution to make an INSERT OR UPDATE 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 EXISTS(SELECT * FROM dbo.SALES WHERE MONTH = 'January') BEGIN UPDATE dbo.SALES SET AMOUNT = 2000 WHERE MONTH = 'January'; END ELSE BEGIN INSERT INTO dbo.SALES ( MONTH, AMOUNT ) VALUES ( N'January', 2000); END
Here is the second solution, perform an UPDATE and check the updated rows.
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 this example of insert into from a SQL Server select statement.