Inserting specific values into an identity column with SQL

by Arthur Fuller | Jul 21, 2005 7:00:00 AM

Tags: Databases, Litigation, SQL, Arthur Fuller, CREATE TABLE TestIdentityGaps, SQL Server Newsletter

Takeaway: Arthur Fuller explores the value of identity columns and the usefulness of their arbitrary values and discusses ways to use SQL Server 2000's IDENTITY_INSERT setting.

Despite your numerous explanations about the value of identity columns and the usefulness of their arbitrary values, some of your coworkers insist upon sequential PKs within a given table. Then, when invoice numbers are missing, they panic, fearing litigation, fraud, or worse.

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

-- Create a test table.
CREATE TABLE TestIdentityGaps
    (
        ID int IDENTITY PRIMARY KEY,
        Description varchar(20)
    )
GO
-- 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')
GO

Now, delete a couple of rows:

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

Since we wrote the code, 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')
GO
SELECT * FROM TestIdentityGaps

You also can't explicitly set the value of an identity column:

-- Try inserting an explicit ID value of 2. Returns a warning.
INSERT INTO TestIdentityGaps (id, Description) VALUES(2, 'Two Point One')
GO

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:

SET IDENTITY_INSERT TestIdentityGaps ON
INSERT INTO TestIdentityGaps (id, Description) VALUES(2, 'Two Point One')
INSERT INTO TestIdentityGaps (id, Description) VALUES(5, 'Five Point One')
GO
SELECT * FROM TestIdentityGaps

Now you can see that the new rows have been inserted using the specified primary keys.

Note: The IDENTITY_INSERT setting 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.

You can insert specific values into a table with an Identity column, but, to do so, you must first set the IDENTITY_INSERT value to ON. If you don't, you'll receive an error message. Even if you set the IDENTITY_INSERT value to ON and then attempt to insert an existing value, you'll receive an error message.

TechRepublic's free SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system. Automatically subscribe today!

6 comment(s)