SQL Server- Insert Values into Identity column

In my previous post Identity Column in SQL Server , I explained about Identity column and how you can create Identity column in table.In this post , I am going to show you that how you can insert values into Identity column.

In previous posts, I explained STUFF Function, LEN Function, UNICODE Function, LEFT Function, CHARINDEX Function, CHAR Function, ASCII Function, Simple script to backup all SQL Server databases, Table-Valued Parameters and some other articles related to SQL Server , ASP.Net, C#.

To insert the values into Identity column, you have need to turn on the IDENTITY_INSERT option.

To illustrate it, Lets create a new table that has an Identity column.

--Creating Table with Identity Column

Create Table MyTestTable

(

ID int Identity(1,1),

Name varchar(50)

)

Now try to insert the data into above created table.

--Insert data into table

Insert into MyTestTable(ID,Name)

values(1,'Manish')

Executing the above code, you will get the following error message.

To fix this, you simply need to turn on the IDENTITY_INSERT option. Keep in mind, the minimum permissions needed to perform this action is database owner.

SET IDENTITY_INSERT dbo.MyTestTable ON;

Insert into MyTestTable(ID,Name)

values(1,'Manish')

SET IDENTITY_INSERT dbo.MyTestTable OFF;

Now execute the above code, you will get the success message.

Note:-Don't forget to turn 'OFF' the IDENTITY_INSERT option.Otherwise, you will not be able to turn this feature on for any other table from within the same session until it is off for this table.

I hope you enjoyed this article. I would like to have any feedback from you. Your valuable feedback, question, or comments about this article are always welcome.