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 the SQL server.

"Identity_Insert" Statement

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

Syntax

Set Identity_insert table_name On / Off

Example

Let us consider the below table: -

Picture showing the design view of the table with the primary key

Click to Enlarge

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

Insert into TestTable 
values 
  (1, 'Test1')

You will get the 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 table, you will see that data gets inserted into the table.

Picture showing inserting the value in the primary key column by setting Identity_insert On

Click to Enlarge

Please do remember to execute the following statement.

Set Identity_insert TestTable Off

Video

Posted By -	Karan Gupta

Posted On -	Wednesday, November 8, 2017

Query/Feedback

Your Email Id		**

Subject		*

Query/Feedback	Characters remaining 250	**