Home » SQL SERVER SET IDENTITY_INSERT

SQL SERVER SET IDENTITY_INSERT

SQL Server IDENTITY_INSERT allows explicit values to be inserted into the identity column of a table.  





The IDENTITY_INSERT statement must be set ON to insert explicit value for identity column. By default IDENTITY_INSERT is OFF.

SYNTAX

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

database_name
Is the name of the database in which the specified table exists.

schema_name
Is the name of the schema to which the table belongs.

table_name
Is the name of a table with an identity column.

Lets Look at an example of SET IDENTITY_INSERT in SQL SERVER.

Following example demonstrate how IDENTITY_INSERT is used to given identity value explicitly for identity column.

We create a table -X which have a identity column ID. Lets check if we try to pass explicit value for identity column ID.

CREATE TABLE X (ID INT IDENTITY(1,1) ) ;

INSERT INTO X (ID) VALUES (1);

 

As we see, It gives an error of an explicit value for the identity column in table X and also suggesting that if we want to insert explicit value for identity column then IDENTITY_INSERT must be turned ON.

Now we try to run above statement again after setting IDENTITY_INSERT ON .

SET IDENTITY_INSERT dbo.X ON

INSERT INTO X (ID) VALUES (1);

SET IDENTITY_INSERT dbo.X OFF 

 

As we see , explicit value for identity column is inserted into table.

 

In above statement , We must specify the column list during the insert otherwise it will not allow explicit value for identity column and throw an error.

As we see in following statement even after IDENTITY_INSERT ON , still it gives an error of explicit values for identity column as we have to specify the column name during the insert.

 




Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.