Inserting explicit values into IDENTITY column
This article outlines how to insert explicit values into IDENTITY column. IDENTITY_INSERT setting is used for this purpose. This article outlines how to set IDENTITY_INSERT setting for a table ON an OFF.
It is not usually possible to insert an explicit value into a column that is specified as IDENTITY column. However,
sometimes it became essential to override this behavior temporarily; for example when setting up application's base data
in a database.
IDENTITY_INSERT setting is used for this purpose. When it is set 'ON' for a table, we can insert explicit values into
IDENTITY column for that table.
To illustrate this, create a table in the database with IDENTITY column,
CREATE TABLE testtable
(
ID INT IDENTITY (1, 1) NOT NULL,
Name VARCHAR(50) NOT NULL
)
If you try to insert explicit value for identity column you will get an error.
INSERT INTO testtable(ID, Name) VALUES (1, 'kadjd')
To enable insertion of IDENTITY values use the SET IDENTITY_INSERT command as below,
SET IDENTITY_INSERT testtable ON
now you can insert explicit value for identity column without getting any error. Once you have completed inserts on table
then set the IDENTITY_INSERT to default off for the table as shown below:
SET IDENTITY_INSERT testtable OFF