SQL Server: Insert values into Identity Column

 SQL Server     sbo     Updated on Sep 03, 2013     VIKRAM     14407   

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 ServerHow to get fixed length number format in SQL Server (or) How to prefix zeros to number, to get fixed length number format in SQL ServerSplit 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.

select * from Customer

Output:

Customers data.

  Related Articles
SQL Server: Variables
SQL Server: Differences between CHAR, NCHAR, VARCHAR and NVARCHAR
SQL Server: System Databases
ASP.Net: List View Basic Example
JQuery: How to replace string in JQuery
Java Script: How to replace string in Java Script
C#: How to replace string in C#
SQL Server: How to replace string in SQL Server
C#: How to convert string to int in C#
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
  Recent Articles
SQL Server: Variables
SQL Server: Differences between CHAR, NCHAR, VARCHAR and NVARCHAR
SQL Server: System Databases
ASP.Net: List View Basic Example
JQuery: How to replace string in JQuery
Java Script: How to replace string in Java Script
C#: How to replace string in C#
SQL Server: How to replace string in SQL Server
C#: How to convert string to int in C#
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