Nov 8, 2013

How to insert the data into the identity column in sql server

When we will try to insert data into the identity column we will get the error message like:
Cannot insert explicit value for identity column in table '' when IDENTITY_INSERT is set to OFF.
It is due to we cannot insert the data into the identity column directly. To insert the data we have to set the IDENTITY_INSERT property.

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

Where:

database_name: It the name of the database.

schema_name: It is the name of schema.

table_name: It is name of the table.

By default it is off for all tables.

Inserting rows in the table with identity columns:
When IDENTITY_INSERT is set to off we cannot insert the data into the identity column while when it is set to on we can insert. For example:
Suppose we have created table by executing following query:

CREATE TABLE Student(
    RollNo BIGINT IDENTITY,
    Name VARCHAR(100),
)

To insert the data into the RollNo by using INSERT statement we must have to set IDENTITY_INSERT property to ON. For example:

SET IDENTITY_INSERT Student ON

INSERT INTO Student(RollNo,Name) VALUES(100,'Scott')
INSERT INTO Student(RollNo,Name) VALUES(120,'Davis')

SET IDENTITY_INSERT Student OFF

Some important points regarding indentity insert in sql server:

1. Scope of the IDENTITY_INSERT property is session.
Note: When we login or open a new query page or tab we creates a new session in sql server.
Suppose first we will execute the following sql statement in any query page or tab:

SET IDENTITY_INSERT Student ON

Now we will open a new query page or tab that is creating a new session and try to execute following sql query:

INSERT INTO Student(RollNo,Name) VALUES(100,'Scott')

We will get the error message like: An explicit value for the identity column in table 'Student' can only be specified when a column list is used and IDENTITY_INSERT is ON.
Since scope of previous setting is not in the new query page.

2. In any seesion only table can have the IDENTITY_INSERT set to on. For example:
Suppose we have created two tables by executing the following sql statement:

CREATE TABLE Student1(
    RollNo BIGINT IDENTITY,
    Name VARCHAR(100),
)

CREATE TABLE Student2(
    RollNo BIGINT IDENTITY,
    Name VARCHAR(100),
)

Now we execute following sql queries:

SET IDENTITY_INSERT Student1 ON
INSERT INTO Student1(RollNo,Name) VALUES(100,'Scott')

SET IDENTITY_INSERT Student2 ON
INSERT INTO Student2(RollNo,Name) VALUES(100,'Scott')

We will get error message: IDENTITY_INSERT is already ON for table 'master.dbo.Student1'. Cannot perform SET operation for table 'Student2'.
So we must have to set the IDENTITY_INSERT property to off of the table Studen1 before setting the IDENTITY_INSERT property to on in the Student2 table. For example:

SET IDENTITY_INSERT Student1 ON
INSERT INTO Student1(RollNo,Name) VALUES(100,'Scott')
SET IDENTITY_INSERT Student1 OFF

SET IDENTITY_INSERT Student2 ON
INSERT INTO Student2(RollNo,Name) VALUES(100,'Scott')

Now it will work fine. Hence it is good practice to set the IDENTITY_INSERT property to off after using it.

3. It is necessary to explicity specify the columns names in the INSERT statement when there is any identity column in the table. For example:

It is incorrect to write:

INSERT INTO Student VALUES(100,'Scott')

Correct way is:

INSERT INTO Student(RollNo,Name) VALUES(100,'Scott')

No comments:

Post a Comment