If you’ve ever tried to insert values into an identity column in SQL Server, you might’ve seen an error like this:

Cannot insert explicit value for identity column in table ‘Artists’ when IDENTITY_INSERT is set to OFF.

This is normal. An identity column is there for a reason. It automatically populates the column with an incrementing value for each row that’s inserted. Therefore there’s no need for you to insert a value into that column.

However, sometimes you do need to insert a value into an identity column. For example, you could be populating the database with data that needs to retain its own identity values. If this is the case, you’ll need to override the IDENTITY property. Here’s how.

Enable IDENTITY_INSERT

You can override the IDENTITY property by enabling IDENTITY_INSERT. This allows you to insert your own values into an identity column.

Here’s an example:

SET IDENTITY_INSERT Artists ON;
INSERT INTO Artists (ArtistId, ArtistName, ActiveFrom)   
VALUES 
  (1001, 'AC/DC','1973-01-11'), 
  (1002, 'Allan Holdsworth','1969-01-01'),
  (1003, 'Buddy Rich','1919-01-01');
SET IDENTITY_INSERT Artists OFF;

In this example, the Artists table has an identity column (in this case, ArtistId is the identity column). To override the IDENTITY property we simply precede the INSERT statement with SET IDENTITY_INSERT Artists ON. This T-SQL statement allows you to say “I know this table has an identity column but in this case, insert my values instead”.

Note that IDENTITY_INSERT can only be enabled on one table at a time. Therefore it’s a good idea to disable IDENTITY_INSERT immediately after the insert. This puts it back to its original state, and it allows you to override an identity column on a different table if required.

Also, in order to enable IDENTITY_INSERT the user must own the table or have ALTER permission on the table.

Copying Between Tables

Enabling IDENTITY_INSERT can also be useful for when you’re copying data between tables. For example, you could be populating one table from another.

Here’s an example of copying data from the Artists table to the Artists_Archive table:

SET IDENTITY_INSERT Artists_Archive ON;
INSERT INTO Artists_Archive (ArtistId, ArtistName, ActiveFrom)
  SELECT ArtistId, ArtistName, ActiveFrom
	FROM Artists;
SET IDENTITY_INSERT Artists_Archive OFF;

One thing to note is that, if the identity column has a primary key constraint, it will reject any values that already exist in the destination table. So you need to be absolutely sure that overriding the identity column is what you definitely want to do.