{"id":8892,"date":"2022-07-18T06:31:00","date_gmt":"2022-07-18T04:31:00","guid":{"rendered":"https:\/\/expert-only.com\/?p=8892"},"modified":"2022-07-25T14:14:19","modified_gmt":"2022-07-25T12:14:19","slug":"insert-or-update-with-sql-server","status":"publish","type":"post","link":"https:\/\/expert-only.com\/en\/t-sql\/insert-or-update-with-sql-server\/","title":{"rendered":"Insert or Update with SQL Server (Upsert)"},"content":{"rendered":"\n

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

Insert or Update queries with SQL Server to avoid the unique key violation error (SQL Upsert)<\/h2>\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

-- 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
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.<\/strong><\/p>\n\n\n\n
Two SQL Server approaches for the UPSERT script <\/h2>\n\n\n\n
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
The first solution to make an UPSERT with Microsoft SQL Server is done in two steps<\/strong><\/h3>\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
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
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
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
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"},"yoast_head":"\nInsert or Update with SQL Server (Upsert) - T-SQL<\/title>\n<meta name=\"description\" content=\"SQL Server Insert or Update query to avoid unique key violation error, the SQL Upsert script do an insert, check the result and then update.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/expert-only.com\/en\/t-sql\/insert-or-update-with-sql-server\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Insert or Update with SQL Server (Upsert)\" \/>\n<meta property=\"og:description\" content=\"SQL Server Insert or Update query to avoid unique key violation error, the SQL Upsert script do an insert, check the result and then update.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/expert-only.com\/en\/t-sql\/insert-or-update-with-sql-server\/\" \/>\n<meta property=\"og:site_name\" content=\"SQL and IT Tutorials\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/ExpertOnlyCom\/\" \/>\n<meta property=\"article:published_time\" content=\"2022-07-18T04:31:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2022-07-25T12:14:19+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/expert-only.com\/wp-content\/uploads\/2018\/06\/idea-93432E0FFA4_1920x1080.jpeg\" \/>\n\t<meta property=\"og:image:width\" content=\"1920\" \/>\n\t<meta property=\"og:image:height\" content=\"1080\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Expert-Only\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@expert_only\" \/>\n<meta name=\"twitter:site\" content=\"@expert_only\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Expert-Only\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/expert-only.com\/en\/t-sql\/insert-or-update-with-sql-server\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/expert-only.com\/en\/t-sql\/insert-or-update-with-sql-server\/\"},\"author\":{\"name\":\"Expert-Only\",\"@id\":\"https:\/\/expert-only.com\/en\/#\/schema\/person\/406a9576b52944f018739a42046873ef\"},\"headline\":\"Insert or Update with SQL Server (Upsert)\",\"datePublished\":\"2022-07-18T04:31:00+00:00\",\"dateModified\":\"2022-07-25T12:14:19+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/expert-only.com\/en\/t-sql\/insert-or-update-with-sql-server\/\"},\"wordCount\":443,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/expert-only.com\/en\/#organization\"},\"image\":{\"@id\":\"https:\/\/expert-only.com\/en\/t-sql\/insert-or-update-with-sql-server\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2018\/06\/idea-93432E0FFA4_1920x1080.jpeg\",\"articleSection\":[\"T-SQL\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/expert-only.com\/en\/t-sql\/insert-or-update-with-sql-server\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/expert-only.com\/en\/t-sql\/insert-or-update-with-sql-server\/\",\"url\":\"https:\/\/expert-only.com\/en\/t-sql\/insert-or-update-with-sql-server\/\",\"name\":\"Insert or Update with SQL Server (Upsert) - T-SQL\",\"isPartOf\":{\"@id\":\"https:\/\/expert-only.com\/en\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/expert-only.com\/en\/t-sql\/insert-or-update-with-sql-server\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/expert-only.com\/en\/t-sql\/insert-or-update-with-sql-server\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2018\/06\/idea-93432E0FFA4_1920x1080.jpeg\",\"datePublished\":\"2022-07-18T04:31:00+00:00\",\"dateModified\":\"2022-07-25T12:14:19+00:00\",\"description\":\"SQL Server Insert or Update query to avoid unique key violation error, the SQL Upsert script do an insert, check the result and then update.\",\"breadcrumb\":{\"@id\":\"https:\/\/expert-only.com\/en\/t-sql\/insert-or-update-with-sql-server\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/expert-only.com\/en\/t-sql\/insert-or-update-with-sql-server\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/expert-only.com\/en\/t-sql\/insert-or-update-with-sql-server\/#primaryimage\",\"url\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2018\/06\/idea-93432E0FFA4_1920x1080.jpeg\",\"contentUrl\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2018\/06\/idea-93432E0FFA4_1920x1080.jpeg\",\"width\":1920,\"height\":1080},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/expert-only.com\/en\/t-sql\/insert-or-update-with-sql-server\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"en\",\"item\":\"https:\/\/expert-only.com\/en\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Insert or Update with SQL Server (Upsert)\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/expert-only.com\/en\/#website\",\"url\":\"https:\/\/expert-only.com\/en\/\",\"name\":\"SQL and IT Tutorials\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\/\/expert-only.com\/en\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/expert-only.com\/en\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/expert-only.com\/en\/#organization\",\"name\":\"Expert-Only\",\"url\":\"https:\/\/expert-only.com\/en\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/expert-only.com\/en\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/09\/cropped-logo_Expert-Only.jpg\",\"contentUrl\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/09\/cropped-logo_Expert-Only.jpg\",\"width\":381,\"height\":174,\"caption\":\"Expert-Only\"},\"image\":{\"@id\":\"https:\/\/expert-only.com\/en\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.facebook.com\/ExpertOnlyCom\/\",\"https:\/\/x.com\/expert_only\",\"https:\/\/www.youtube.com\/channel\/UCMS5sR_FwAetB0FmciNvUaA\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/expert-only.com\/en\/#\/schema\/person\/406a9576b52944f018739a42046873ef\",\"name\":\"Expert-Only\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/expert-only.com\/en\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/084b15660763ff5b13bb60b2f52f97bb?s=96&d=identicon&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/084b15660763ff5b13bb60b2f52f97bb?s=96&d=identicon&r=g\",\"caption\":\"Expert-Only\"}}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Insert or Update with SQL Server (Upsert) - T-SQL","description":"SQL Server Insert or Update query to avoid unique key violation error, the SQL Upsert script do an insert, check the result and then update.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/expert-only.com\/en\/t-sql\/insert-or-update-with-sql-server\/","og_locale":"en_US","og_type":"article","og_title":"Insert or Update with SQL Server (Upsert)","og_description":"SQL Server Insert or Update query to avoid unique key violation error, the SQL Upsert script do an insert, check the result and then update.","og_url":"https:\/\/expert-only.com\/en\/t-sql\/insert-or-update-with-sql-server\/","og_site_name":"SQL and IT Tutorials","article_publisher":"https:\/\/www.facebook.com\/ExpertOnlyCom\/","article_published_time":"2022-07-18T04:31:00+00:00","article_modified_time":"2022-07-25T12:14:19+00:00","og_image":[{"width":1920,"height":1080,"url":"https:\/\/expert-only.com\/wp-content\/uploads\/2018\/06\/idea-93432E0FFA4_1920x1080.jpeg","type":"image\/jpeg"}],"author":"Expert-Only","twitter_card":"summary_large_image","twitter_creator":"@expert_only","twitter_site":"@expert_only","twitter_misc":{"Written by":"Expert-Only","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/expert-only.com\/en\/t-sql\/insert-or-update-with-sql-server\/#article","isPartOf":{"@id":"https:\/\/expert-only.com\/en\/t-sql\/insert-or-update-with-sql-server\/"},"author":{"name":"Expert-Only","@id":"https:\/\/expert-only.com\/en\/#\/schema\/person\/406a9576b52944f018739a42046873ef"},"headline":"Insert or Update with SQL Server (Upsert)","datePublished":"2022-07-18T04:31:00+00:00","dateModified":"2022-07-25T12:14:19+00:00","mainEntityOfPage":{"@id":"https:\/\/expert-only.com\/en\/t-sql\/insert-or-update-with-sql-server\/"},"wordCount":443,"commentCount":0,"publisher":{"@id":"https:\/\/expert-only.com\/en\/#organization"},"image":{"@id":"https:\/\/expert-only.com\/en\/t-sql\/insert-or-update-with-sql-server\/#primaryimage"},"thumbnailUrl":"https:\/\/expert-only.com\/wp-content\/uploads\/2018\/06\/idea-93432E0FFA4_1920x1080.jpeg","articleSection":["T-SQL"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/expert-only.com\/en\/t-sql\/insert-or-update-with-sql-server\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/expert-only.com\/en\/t-sql\/insert-or-update-with-sql-server\/","url":"https:\/\/expert-only.com\/en\/t-sql\/insert-or-update-with-sql-server\/","name":"Insert or Update with SQL Server (Upsert) - T-SQL","isPartOf":{"@id":"https:\/\/expert-only.com\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/expert-only.com\/en\/t-sql\/insert-or-update-with-sql-server\/#primaryimage"},"image":{"@id":"https:\/\/expert-only.com\/en\/t-sql\/insert-or-update-with-sql-server\/#primaryimage"},"thumbnailUrl":"https:\/\/expert-only.com\/wp-content\/uploads\/2018\/06\/idea-93432E0FFA4_1920x1080.jpeg","datePublished":"2022-07-18T04:31:00+00:00","dateModified":"2022-07-25T12:14:19+00:00","description":"SQL Server Insert or Update query to avoid unique key violation error, the SQL Upsert script do an insert, check the result and then update.","breadcrumb":{"@id":"https:\/\/expert-only.com\/en\/t-sql\/insert-or-update-with-sql-server\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/expert-only.com\/en\/t-sql\/insert-or-update-with-sql-server\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/expert-only.com\/en\/t-sql\/insert-or-update-with-sql-server\/#primaryimage","url":"https:\/\/expert-only.com\/wp-content\/uploads\/2018\/06\/idea-93432E0FFA4_1920x1080.jpeg","contentUrl":"https:\/\/expert-only.com\/wp-content\/uploads\/2018\/06\/idea-93432E0FFA4_1920x1080.jpeg","width":1920,"height":1080},{"@type":"BreadcrumbList","@id":"https:\/\/expert-only.com\/en\/t-sql\/insert-or-update-with-sql-server\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"en","item":"https:\/\/expert-only.com\/en\/"},{"@type":"ListItem","position":2,"name":"Insert or Update with SQL Server (Upsert)"}]},{"@type":"WebSite","@id":"https:\/\/expert-only.com\/en\/#website","url":"https:\/\/expert-only.com\/en\/","name":"SQL and IT Tutorials","description":"","publisher":{"@id":"https:\/\/expert-only.com\/en\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/expert-only.com\/en\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/expert-only.com\/en\/#organization","name":"Expert-Only","url":"https:\/\/expert-only.com\/en\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/expert-only.com\/en\/#\/schema\/logo\/image\/","url":"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/09\/cropped-logo_Expert-Only.jpg","contentUrl":"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/09\/cropped-logo_Expert-Only.jpg","width":381,"height":174,"caption":"Expert-Only"},"image":{"@id":"https:\/\/expert-only.com\/en\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/ExpertOnlyCom\/","https:\/\/x.com\/expert_only","https:\/\/www.youtube.com\/channel\/UCMS5sR_FwAetB0FmciNvUaA"]},{"@type":"Person","@id":"https:\/\/expert-only.com\/en\/#\/schema\/person\/406a9576b52944f018739a42046873ef","name":"Expert-Only","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/expert-only.com\/en\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/084b15660763ff5b13bb60b2f52f97bb?s=96&d=identicon&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/084b15660763ff5b13bb60b2f52f97bb?s=96&d=identicon&r=g","caption":"Expert-Only"}}]}},"_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}]}}