{"id":3319,"date":"2012-12-04T20:21:53","date_gmt":"2012-12-04T20:21:53","guid":{"rendered":"http:\/\/craftydba.com\/?p=3319"},"modified":"2017-10-08T23:48:48","modified_gmt":"2017-10-08T23:48:48","slug":"identity-column-insert","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=3319","title":{"rendered":"Identity Column Insert"},"content":{"rendered":"

\"\"<\/a><\/p>\n

Today, I want to talk about how to insert a value into a identity column. Most of the time, the auto increment or IDENTITY<\/a> column will just be happy by being left alone. Very seldom, a power user does something stupid like turn off the foreign key constraint and remove a key from a reference table. How do we add back the key?

<\/p>\n

I will be dusting off the [WILDLIFE] database from a prior article. This database has one table named [ANIMALS]. It has 445 unique animal names each with a unique identifier. I will assume the database is created and the table is loaded. <\/p>\n

The code snippet below removes the row identified by ID 245, a “Meerkat”. It then tries to add back the animal. It fails since identity insert is set off as a default.<\/p>\n

\r\n--\r\n--  Remove & add back item 245\r\n--\r\n\r\n-- Remove one item\r\nDELETE FROM [WILDLIFE].[dbo].[ANIMALS] WHERE ID = 245;\r\nGO\r\n\r\n-- Show the item is gone\r\nSELECT * FROM [WILDLIFE].[dbo].[ANIMALS] WHERE ID = 245;\r\nGO\r\n\r\n-- Try adding value mid sequence\r\nINSERT INTO [WILDLIFE].[dbo].[ANIMALS] VALUES (245, 'Meerkat');\r\nGO\r\n<\/pre>\n<\/p>\n
\"\"<\/a><\/p>\n
There is a table option that can be set to allow identity inserts.  The snippet below turns on identity insert, adds the one row, and then turns it off.<\/p>\n
\r\n--\r\n--  Use identity insert on to get job done\r\n--\r\n\r\n-- Allow id inserts \r\nSET IDENTITY_INSERT [WILDLIFE].[dbo].[ANIMALS] ON\r\nGO\r\n\r\n-- Try adding value mid sequence\r\nINSERT INTO [WILDLIFE].[dbo].[ANIMALS] (ID, NAME) VALUES (245, 'Meerkat');\r\nGO\r\n\r\n-- Dis-allow id inserts \r\nSET IDENTITY_INSERT [WILDLIFE].[dbo].[ANIMALS] OFF\r\nGO\r\n\r\n-- Show the item is back\r\nSELECT * FROM [WILDLIFE].[dbo].[ANIMALS] WHERE ID = 245;\r\nGO\r\n<\/pre>\n<\/p>\n
\"\"<\/a><\/p>\n
Most of the time, you will not have to insert records mid sequence into a identity column.  If you find an instance in which you have to, use the SET IDENTITY_INSERT<\/a> table option to perform the task.<\/p>\n","protected":false},"excerpt":{"rendered":"
Today, I want to talk about how to insert a value into a identity column. Most of the time, the auto increment or IDENTITY column will just be happy by being left alone. Very seldom, a power user does something stupid like turn off the foreign key constraint and remove a key from a reference table. How do we add back the key? I will be dusting off the [WILDLIFE] database from a prior article. This database has one table named [ANIMALS]. It has 445 unique animal names each with…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9],"tags":[33,12,660,30,15,659,658,28,29],"class_list":["post-3319","post","type-post","status-publish","format-standard","hentry","category-db-dev","tag-delete","tag-free-code","tag-identity","tag-insert","tag-john-f-miner-iii","tag-set-identity_insert-off","tag-set-identity_insert-on","tag-sql-server","tag-tsql"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/3319","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3319"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/3319\/revisions"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3319"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3319"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3319"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}