{"id":66,"date":"2009-01-22T21:43:34","date_gmt":"2009-01-23T02:43:34","guid":{"rendered":"\/\/www.beauchamp.me\/techno\/?p=66"},"modified":"2012-03-22T09:28:26","modified_gmt":"2012-03-22T14:28:26","slug":"how-to-insert-into-the-pk-column-when-it-is-an-auto-number","status":"publish","type":"post","link":"https:\/\/www.beauchamp.me\/techno\/blog\/2009\/01\/22\/how-to-insert-into-the-pk-column-when-it-is-an-auto-number\/","title":{"rendered":"How to insert into the PK column when it is an auto-number"},"content":{"rendered":"<p>Normally, trying to insert data into an identity column (autoincremental column) results in an error message. However, there are cases when we need to do this, and using SET IDENTITY_INERT, it is possible. Here is how:<\/p>\n<pre class=\"brush:sql\">SET IDENTITY_INSERT MyTable ON\r\n\r\nINSERT INTO MyTable(MyTableId, ...)\r\nVALUES (42, ...)\r\n\r\nSET IDENTITY_INSERT MyTable OFF<\/pre>\n<p>Here is the message you will get if you don&#8217;t do this:<\/p>\n<blockquote><p>Cannot insert explicit value for identity column in table &#8216;MyTable&#8217; when IDENTITY_INSERT is set to OFF.<\/p><\/blockquote>\n<p>This is valid only for inserts. It is not possible to update an identity column using this method. If you want to change a row id, you will have to re-insert the row with the new id and delete the original one. Let&#8217;s say you want the row with id 42 to really have id 100:<\/p>\n<pre class=\"brush:sql\">SET IDENTITY_INSERT MyTable ON\r\n\r\nINSERT INTO MyTable\r\n(MyTableId, ...)\r\nSELECT 100 AS MyTableId, ... FROM MyTable\r\nWHERE MyTableId=42\r\n\r\nDELETE FROM MyTable\r\nWHERE MyTableId=42\r\n\r\nSET IDENTITY_INSERT MyTable OFF<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Normally, trying to insert data into an identity column (autoincremental column) results in an error message. However, there are cases when we need to do this, and using SET IDENTITY_INERT, it is possible. Here is how: SET IDENTITY_INSERT MyTable ON INSERT INTO MyTable(MyTableId, &#8230;) VALUES (42, &#8230;) SET IDENTITY_INSERT MyTable OFF Here is the message [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[3],"tags":[],"class_list":["post-66","post","type-post","status-publish","format-standard","hentry","category-sql-server-2005"],"_links":{"self":[{"href":"https:\/\/www.beauchamp.me\/techno\/wp-json\/wp\/v2\/posts\/66","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.beauchamp.me\/techno\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.beauchamp.me\/techno\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.beauchamp.me\/techno\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.beauchamp.me\/techno\/wp-json\/wp\/v2\/comments?post=66"}],"version-history":[{"count":12,"href":"https:\/\/www.beauchamp.me\/techno\/wp-json\/wp\/v2\/posts\/66\/revisions"}],"predecessor-version":[{"id":189,"href":"https:\/\/www.beauchamp.me\/techno\/wp-json\/wp\/v2\/posts\/66\/revisions\/189"}],"wp:attachment":[{"href":"https:\/\/www.beauchamp.me\/techno\/wp-json\/wp\/v2\/media?parent=66"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.beauchamp.me\/techno\/wp-json\/wp\/v2\/categories?post=66"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.beauchamp.me\/techno\/wp-json\/wp\/v2\/tags?post=66"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}