INSERTING YOUR OWN IDENTITIES

When defining a table with an identity column, it is sometimes

necessary to override the system-generated ID and replace it with your

own or a legacy ID. There are a few rules you must follow when

performing these tasks. Assume the following table structure and

scenario:

CREATE TABLE TableX (IDCol INT IDENTITY(1,1) NOT NULL,

Fname VARCHAR(10) NOT NULL,

LName VARCHAR(10) NOT NULLl)

Normally, you can INSERT into a table that contains an ID column

without specifying the actual ID column in the values list. For

example:

INSERT INTO TableX VALUES ('John', 'Smith')

If you attempt to INSERT into the ID column

INSERT INTO TableX VALUES (1, 'John', 'Smith')

you should receive an error similar to "An explicit value for the

identity column in table 'TableX' can only be specified when a column

list is used and IDENTITY_INSERT is ON."

Use the following rules to INSERT your own identities.

1. Make use of the SET IDENTITY_INSERT <TableName> ON|OFF statement.

2. Qualify your INSERT statement with the column name(s).

3. Once completed, turn off the SET IDENTITY_INSERT <TableName> ON|OFF

statement.

SET IDENTITY_INSERT TableX ON

INSERT INTO TableX (IDCol, Fname, Lname)

VALUES (1,'John', 'Smith')

INSERT INTO TableX (IDCol, Fname, Lname)

VALUES (2,'Mary', 'Doe')

INSERT INTO TableX (IDCol, Fname, Lname)

VALUES (3,'Steve', 'Martin')

SET IDENTITY_INSERT TableX OFF

These steps provide a powerful mechanism for inserting legacy IDs as

well as your own IDs into a table defined with an identity column.