How to insert values to identity column in SQL Server

Identity Columns can not be specified in Insert list because SQL Server automatically sets this value.

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

In order to Insert into Identity column

set IDENTITY_INSERT  [tablename]  ON;

insert statement here

set IDENTITY_INSERT  [tablename]  OFF;

--otherwise default nature of auto increment will not work, so u need to set to OFF.



for ex:

Set IDENTITY_INSERT  [dbo].[Sports]  ON;

Insert into Sports values(1,'Basket ball','IN');

the above insert statement throws error shown below.

 Msg 8101, Level 16, State 1, Line 2
An explicit value for the identity column in table 'Sports' can only be specified when a column list is used and IDENTITY_INSERT is ON.


so u need to specify column list in the insert statement.

Insert into Sports(ID,name,country) values(1,'Basket ball','IN');

set IDENTITY_INSERT  [dbo].[sports]  OFF;

This is how manually inserting values into Identity Column.

tags: How to insert values to identity column in SQL Server,This is how manually inserting values into Identity Column,set IDENTITY_INSERT    OFF,set IDENTITY_INSERT    ON