Insert specific values in an identity column

from the Artful SQL Server & Access Tips List

You have an invoice table with a column that the spec requires to be populated manually with sequential values. Later it turns out that a few values are missing. There is panic. Might there be litigation? Fraud?

To appreciate the problem, create a table with an identity column and populate it with a few rows:

CREATE TABLE TestIdentityGaps
        Description varchar(20)
-- Insert some values. The word INTO is optional:
INSERT [INTO] TestIdentityGaps (Description) VALUES ('One')
INSERT [INTO] TestIdentityGaps (Description) VALUES ('Two')
INSERT [INTO] TestIdentityGaps (Description) VALUES ('Three')
INSERT [INTO] TestIdentityGaps (Description) VALUES ('Four')
INSERT [INTO] TestIdentityGaps (Description) VALUES ('Five')
INSERT [INTO] TestIdentityGaps (Description) VALUES ('Six')

Now delete a couple of rows:

DELETE TestIdentityGaps
WHERE Description IN('Two', 'Five')

We wrote the code, so we know that values 'Two' and 'Five' are missing. We want to insert two rows to fill these gaps. Two simple INSERT statements won't fill the bill; rather, they'll create PKs at the end of the sequence:

INSERT [INTO] TestIdentityGaps (Description) VALUES ('Two Point One')
INSERT [INTO] TestIdentityGaps (Description) VALUES ('Five Point One')

Trying to explicitly set the value of an identity column elicits a warning:

INSERT INTO TestIdentityGaps (id, Description) VALUES(2, 'Two Point One')

As a way to work around this problem, SQL Server 2000 provides the setting IDENTITY_INSERT. To force the insertion of a row with a specific value, issue the command and then follow it with your specific inserts:

INSERT INTO TestIdentityGaps (id, Description) VALUES(2, 'Two Point One')
INSERT INTO TestIdentityGaps (id, Description) VALUES(5, 'Five Point One')

The new rows have been inserted using the specified PK values.

Note that a setting of IDENTITY_INSERT applies to one table within a database at any given moment. If you need to plug gaps in more than one table, you must explicitly identify each table with a specific command.

Note also that in before setting the IDENTITY_INSERT value for a column which is an Identity column, you must first set IDENTITY_INSERT ON.

Return to the Artful SQL Server & Access Tips page