SQL 2000+Inserting Values into Identity Columns
Identity columns are used in SQL Server to automatically generate sequential integer values as new rows are inserted into a table. Under normal circumstances an explicit value cannot be specified. However, sometimes this is essential.
Identity Insert
When a table contains a column with an identity specification, a new integer value is automatically generated in the column for every row inserted into the table. It is not normally possible to insert an explicit value into that column.
Sometimes it is necessary to override this behaviour to temporarily allow explicit values to be inserted. This may be required when setting up your application's base data in a database. It is also useful when providing support to users if, for example, data has inadvertently been deleted and must be replaced using the correct numbers in the identity column.
To permit the insertion of identity values, the IDENTITY_INSERT setting must be changed. When set to "ON", the identity values are writeable for new rows. This setting can only be enabled for a single table at any time and should be set back to "OFF" once the data manipulation is completed.
To demonstrate, create a new table in a database using the following command:
CREATE TABLE IdentityTest
(
EmployeeID INT IDENTITY (1, 1) NOT NULL,
EmployeeName VARCHAR(50) NOT NULL
)
If you try to insert a row into this table, you will receive an error. Try inserting a row using the following statement:
INSERT INTO IdentityTest (EmployeeID, EmployeeName) VALUES (1, 'Olivia Branch')
To enable modification of the identity values in the table, use the SET IDENTITY_INSERT command for the table as follows:
SET IDENTITY_INSERT IdentityTest ON
Executing the previous insert statement is now possible. Once you have completed all of the insertions, you should return the table to its default state using the same command but with a value of "OFF":
SET IDENTITY_INSERT IdentityTest OFF
15 September 2008