Articles → SQL SERVER → Insert value in Identity column in SQL server

Insert value in Identity column in SQL server

In this article, we will discuss how to insert value in the identity column in SQL server.

Identity_insert statement

By setting Identity_insert as ‘On’, we can insert value in identity column in SQL server.

Syntax

Set Identity_insert table_name On/Off

Example

For this example, let us consider the following table

Click to Enlarge

In the above table, the column ‘id’ is auto increment field. Let us see, what will happen when we insert value in ‘id’ column using the following command

Insert into TestTable values(1, 'Test1')

You will get following error - Msg 8101, Level 16, State 1, Line 3

An explicit value for the identity column in table 'TestTable' can only be specified when a column list is used and IDENTITY_INSERT is ON

Now let us use the identity_insert statement.

Set Identity_insert TestTable On
Insert into TestTable (id, Name) values(1, 'Test1')
Set Identity_insert TestTable Off

Once you execute the above statements and check the TestTable, you will see that data gets inserted into the table.

Click to Enlarge

It is important to note that I have set identity_insert as Off after the insert is done. This step is optional and if you want, you can skip this step.

Video

Posted By -	Karan Gupta

Posted On -	Wednesday, November 8, 2017

Query/Feedback

Your Email Id

Subject

Query/Feedback	Characters remaining 250