Friday, April 5, 2013

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 ServerASP.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.

Cannot insert explicit value for identity column in table
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.

No comments:

Post a Comment

If You Enjoyed This Post Please Take 5 Seconds To Share It.

^ Scroll to Top hgpromo