Using IDENTITY_INSERT

The only way to insert values into a field that is defined as an “IDENTITY” (or autonumber) field, is to set the IDENTITY_INSERT option to “ON” prior to inserting data into the table.

To illustrate, let’s create a table that has an identity column defined.

[cc lang=”sql”]
— Create table with identity column
CREATE TABLE MyOrders
(
OrderID int IDENTITY(1,1),
ProductName varchar(20)
);
— Now try to insert into the table
INSERT INTO dbo.MyOrders
(
OrderID,
ProductName
)
VALUES
(
1,
‘socks’
);
[/cc]

Executing the above code, we see that we get the following error:
Cannot insert explicit value for identity column in table ‘MyOrders’ when IDENTITY_INSERT is set to OFF.

To fix this, we simply need to set the IDENTITY_INSERT to On. Keep in mind, the minimum permissions needed to perform this action is database owner (dbo).

[cc lang=”sql”]

SET IDENTITY_INSERT dbo.MyOrders ON;
INSERT INTO dbo.MyOrders
(
OrderID,
ProductName
)
VALUES
(
1,
‘socks’
);
SET IDENTITY_INSERT dbo.MyOrders OFF;
[/cc]

You want to make sure to turn this off after it’s used. Otherwise, you will not be able to turn this feature on for any other table from within the same session until it is off for this table.