SQL Server

SQL Server Tutorial – Insert Values into Identity column

In this post , I am going to show you that how you can insert values into Identity column.
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.
Happy coding!!
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s