{"id":8574,"date":"2025-01-26T19:39:14","date_gmt":"2025-01-26T19:39:14","guid":{"rendered":"http:\/\/putridparrot.com\/blog\/?p=8574"},"modified":"2025-01-26T19:39:14","modified_gmt":"2025-01-26T19:39:14","slug":"sql-server-and-identity_insert","status":"publish","type":"post","link":"https:\/\/putridparrot.com\/blog\/sql-server-and-identity_insert\/","title":{"rendered":"SQL Server and IDENTITY_INSERT"},"content":{"rendered":"<p>I&#8217;m creating an SQL script to seed my SQL Server database, the tables include a primary key with autoincrement set (i.e. IDENTITY(1,1)). Inserting data within supplying a primary key works fine, but if you decide to seed the primary key data as well then I need to make a couple of changes to my SQL script.<\/p>\n<p>Why would I want to seed the primary key if it&#8217;s autoincrementing, you may ask. The reason is that I intend to also seed some of the relationship data as well and this ofcourse means I already have the table&#8217;s primary key value (because I set it) and thus can easily create the relationship links.<\/p>\n<p>What you need to do is wrap your INSERTs for a specific table in, so for example below we have a Country table and the primary key is Id.<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nSET IDENTITY_INSERT &#x5B;dbo].&#x5B;Country] ON\r\n\r\nINSERT INTO &#x5B;dbo].&#x5B;Country] (&#x5B;Id], &#x5B;Name]) \r\nVALUES (1, &#039;Australia&#039;)\r\n\r\nSET IDENTITY_INSERT &#x5B;dbo].&#x5B;Country] OFF\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>I&#8217;m creating an SQL script to seed my SQL Server database, the tables include a primary key with autoincrement set (i.e. IDENTITY(1,1)). Inserting data within supplying a primary key works fine, but if you decide to seed the primary key data as well then I need to make a couple of changes to my SQL [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[724],"tags":[],"class_list":["post-8574","post","type-post","status-publish","format-standard","hentry","category-sql-server-2"],"jetpack_sharing_enabled":true,"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/posts\/8574","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/comments?post=8574"}],"version-history":[{"count":2,"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/posts\/8574\/revisions"}],"predecessor-version":[{"id":11269,"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/posts\/8574\/revisions\/11269"}],"wp:attachment":[{"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/media?parent=8574"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/categories?post=8574"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/tags?post=8574"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}