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. <\/p>\n\n\n\n
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.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
Note<\/span><\/strong>: this SQL insert or update inserts data in a very simple table, simply adjust the code to your current project.<\/p>\n\n\n\n After two consecutive inserts for the same month, we got the error message below.<\/p>\n\n\n\n Msg 2627, Level 14, State 1, Line 18 Indeed there are two ways or managing the SQL Server Upsert query. Very similar but simply in the opposite order.<\/p>\n\n\n\n Firstly, we test if the row to insert exists in the table, using the EXISTS<\/a> function. <\/p>\n\n\n\n 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. <\/p>\n\n\n\n In practice we don’t do an INSERT OR UPDATE, but rather an UPDATE OR INSERT.<\/p>\n\n\n\n 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. <\/p>\n\n\n\n Finally, the latter inserts a new row for the month of January, which is the key.<\/p>\n\n\n\n Finally, this article has presented you two ways to check the existence of data before insertion or after an update.<\/p>\n\n\n\n To go further, check out these T-SQL examples to of manage SQL Server functions<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":" 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 …<\/a><\/p>\n<\/div>","protected":false},"author":1,"featured_media":6139,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[454],"tags":[],"class_list":{"0":"post-8892","1":"post","2":"type-post","3":"status-publish","4":"format-standard","5":"has-post-thumbnail","7":"category-t-sql"},"_links":{"self":[{"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/posts\/8892","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/comments?post=8892"}],"version-history":[{"count":0,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/posts\/8892\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/media\/6139"}],"wp:attachment":[{"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/media?parent=8892"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/categories?post=8892"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/tags?post=8892"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}-- Delete it the table if it already exists\nIF exists (\n\tSELECT 1 FROM sys.objects\n\tWHERE object_id = object_id(N'[dbo].[SALES]') \n\t\tAND type in (N'U')\n)\nBEGIN DROP TABLE [dbo].[SALES]\nEND\nGO\n\n-- Create the sample T-SQL table with the column MONTH declared as UNIQUE\nCREATE TABLE [dbo].[SALES]\n(\n\t[MONTH] nvarchar(20) UNIQUE,\n\t[AMOUNT] numeric(5)\n)\nGO\n\n-- Insert two lines of sales data for the January month as example\nINSERT INTO dbo.SALES ( MONTH, AMOUNT ) VALUES ( N'January', 1000);\nINSERT INTO dbo.SALES ( MONTH, AMOUNT ) VALUES ( N'January', 2000);\n<\/pre>\n\n\n\n
The error message that appears looks like this, with an English SQL Server version<\/h3>\n\n\n\n
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.<\/strong><\/p>\n\n\n\nTwo SQL Server approaches for the UPSERT script <\/h2>\n\n\n\n
The first solution to make an UPSERT with Microsoft SQL Server is done in two steps<\/strong><\/h3>\n\n\n\n
IF NOT EXISTS(SELECT * FROM dbo.SALES WHERE MONTH = 'January')\nBEGIN\n INSERT INTO dbo.SALES (MONTH, AMOUNT) \n VALUES ( N'January', 2000);\nEND\nELSE\n BEGIN\n UPDATE dbo.SALES\n SET AMOUNT = 2000\n WHERE MONTH = 'January';\n END\nGO\n<\/pre>\n\n\n\n
The second solution is to UPDATE the line then count the updated rows before inserting new data<\/h3>\n\n\n\n
UPDATE dbo.SALES\n SET AMOUNT = 2000\n WHERE MONTH = 'January';\n \nIF @@ROWCOUNT = 0\nBEGIN\n INSERT INTO dbo.SALES ( MONTH, AMOUNT ) \n VALUES ( N'January', 2000);\nEND;\n<\/pre>\n\n\n\n