Home > SQL Server > Insert value to an Identity column – SET IDENTITY_INSERT – SQL Server

Insert value to an Identity column – SET IDENTITY_INSERT – SQL Server


We all know that we cannot insert a value to an identity column in a table using insert statement. Yes, it is true. But, there is a way that allows us to explicitly insert  and not update a value in the identity column.

SET IDENTITY_INSERT

This is a set statement that allows user to inserted a value into the identity column. It allows us to do so when this set statement is on otherwise we cannot insert value to identity column.

SET IDENTITY_INSERT Syntax

SET IDENTITY_INSERT [ database_name . [ schema_name ] . ] table { ON | OFF }

In this statement database name and schema name is optional. But, we have to use a valid table name along with on or off switch in the set statement. User should have valid permission to perform this operation.

Example

I have already designed a table with identity column. In this first example, I am going to update an identity column to see what is the error message does SQL Server return to the user.

Updating Identity column

pic1Now, inserting a value in to an identity column

pic2Now, I am going to do explicit insert to an identity column using IDENTITY_INSERT set statement.

Current values in the table

pic1Delete and insert a row with value to an identity column. RowID column is the identity column in this table.

pic2Now, I have inserted a row with value to an identity column. But, using this statement we can also insert duplicate identity value to a table. SQL Server will not check for duplicate identity value using  when we use SET Identity_Insert and Insert statement. So, we have to use Primary or unique key constraint to keep valid identity values in the table.

pic3

About these ads
  1. No comments yet.
  1. No trackbacks yet.

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

Follow

Get every new post delivered to your Inbox.

Join 315 other followers

%d bloggers like this: