How to insert the Value in the Identity column of the table ?

In SQL server each table has identity column to auto increment the value , but there is sometimes we need to insert the value in the identity column.

You can use the following syntax to insert the value manually.

Syntax

To Switch ON 

SET IDENTITY_INSERT TABLE_NAME ON

To Switch OFF

SET IDENTITY_INSERT TABLE_NAME OFF

Example

In the below example i tried to insert the value in the identity column of the identitytable, After executing the below query i got the error message in the messages tab as shown below.

After i switched ON the below property and try to execute the statement still i am getting the error message but the message is different.

If we switched ON the below property we have to use the column list in the insert statement.

After using the columns in the insert statement it works fine and executed successfully.

Do not forget to switch OFF the IDENTITY_INSERT property.