Insert value in Identity column of table

Hi,
                        In This article we will see how to insert value in Identity column.Identity field
is usually used as a primary key. When you insert a new record into your table,
this field automatically assign an incremented value from the previous entry.
Usually, you can't insert your own value to this field.
In this article, I am going to expose the tips for inserting your own value to this field.
It is simple and easy. Consider you have the following Emp_Info table.

CREATE TABLE Emp_Info
(
ID int IDENTITY,
Emp_Name varchar(100),
Emp_Address varchar(200)
)

Now, I am trying to insert a record into Emp_Info table with
identity field like as then I will get the error message as shown below.

INSERT INTO Emp_Info(ID,Emp_Name,Emp_Address) VALUES(1,'Ajit Gaikwad','sangli')

Error::

Msg 544, Level 16, State 1, Line 1 Cannot insert explicit value for identity column in table 'Emp_Info' when IDENTITY_INSERT is set to OFF.

A) Allow insert into identity field

You can alllow insert to the identity field by setting IDENTITY_INSERT ON for a particular
table as shown:

SET IDENTITY_INSERT Emp_Info ON

B) Disallow insert into identity field

You can also disalllow insert to the identity field by setting IDENTITY_INSERT OFF for a
particular table as shown:

SET IDENTITY_INSERT Emp_Info OFF

Test:: Insert Value to Identity field

Now, lets see how to insert our own values to identity field ID with in the Emp_Info table.

SET IDENTITY_INSERT Emp_Info ON
INSERT INTO Emp_Info(ID,Name,Address) VALUES(3,'harshal','sangli')
INSERT INTO Emp_Info(ID,Name,Address) VALUES(4,'ram','sangli')

SET IDENTITY_INSERT Emp_Info OFF
INSERT INTO Emp_Info(Name,Address) VALUES('rakesh','bihar')

After Inserting your own value to identity field don't forget to set IDENTITY_INSERT OFF.

Other Popular Posts ::

Column in comma separated strings
Pivot Unpivot data in sql server
Combine two or more column in one column in sql sever
rank fuctions in sql
Get month wise Number of working
Take Back up of table using select statement in sql server
Column data in comma seprated string
Delete duplicate records from table
Date function in sql server
Sql server::String with initial letter in uppercase.
Joins in Sql server
Dynamic Query in sql server
Basic About View
send sql table data via email using store procedure
How to use Store Procedure inside a store procedure
SET ANSI_NULLS in sql server
SET QUOTED_IDENTIFIER in sql
example of Cursor in sql server
basic of Cursor in sql.
delete duplicate records in sql server
select duplicate records in sql server
update duplicate records in sql server
comman table expression(CTE) in sql server
Split Funtions in sql server
Sql queries for sql server
Trigger on Update