Identity Column Insert

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.

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.

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.

Related posts

Leave a Comment