How to insert values into Identity Column in SQL Server?

Introduction:

In this article i will explain how to insert values into identity column in SQL Server.

Description:

In previous articles i explained How to replace string in SQL Server, How to get fixed length number format in SQL Server (or) How to prefix zeros to number, to get fixed length number format in SQL Server, Split function (or) How to Split string in SQL Server and other articles. Now i will explain how to insert values into identity column in SQL Server.

Create Customer table as below:

Create table Customer
(
    CustomerID int Primary Key identity(1,1),
    CustomerName varchar(100),
    CustomerLocation varchar(50)
)

Execute above statements. Customer table will be created.

Now insert customer data into Customer table as shown below:

insert into Customer(CustomerID, CustomerName, CustomerLocation)
values(1, 'Microsoft', 'Hyderabad')

Execute above statements then you will get following error message.

"Cannot insert explicit value for identity column in table 'Customer' when IDENTITY_INSERT is set to OFF."

In order to insert CustomerID into Customer table we need to set IDENTITY_INSERT to ON.

Solution:

1) Set IDENTITY_INSERT to ON.

2) Insert customers data into Customer table.

3) Set IDENTITY_INSERT to OFF.

Set IDENTITY_INSERT Customer ON;

insert into Customer(CustomerID, CustomerName, CustomerLocation)
values(1, 'Microsoft', 'Hyderabad')

insert into Customer(CustomerID, CustomerName, CustomerLocation)
values(2, 'Oracle', 'Hyderabad')

Set IDENTITY_INSERT Customer OFF;

Execute above statements. Now data will be inserted.

Now check Customer table.