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 a unique identifier. I will assume the database is created and the table is loaded.
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
-- -- Remove & add back item 245 -- -- Remove one item DELETE FROM [WILDLIFE].[dbo].[ANIMALS] WHERE ID = 245; GO -- Show the item is gone SELECT * FROM [WILDLIFE].[dbo].[ANIMALS] WHERE ID = 245; GO -- Try adding value mid sequence INSERT INTO [WILDLIFE].[dbo].[ANIMALS] VALUES (245, 'Meerkat'); GO |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
-- -- Use identity insert on to get job done -- -- Allow id inserts SET IDENTITY_INSERT [WILDLIFE].[dbo].[ANIMALS] ON GO -- Try adding value mid sequence INSERT INTO [WILDLIFE].[dbo].[ANIMALS] (ID, NAME) VALUES (245, 'Meerkat'); GO -- Dis-allow id inserts SET IDENTITY_INSERT [WILDLIFE].[dbo].[ANIMALS] OFF GO -- Show the item is back SELECT * FROM [WILDLIFE].[dbo].[ANIMALS] WHERE ID = 245; GO |
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 table option to perform the task.
