CODEWITHSUNDEEP
sqlsql-server
Achilles
Achilles

Reputation: 11299

How to specify a specific value for an auto increment column in SQL Server?

I have an auto increment identity column in sql server, but I want to specify a specific value for one of the rows. So the number scheme would be as follows:

1 ...,
2....,
999 - for the reserved entry,
3....,
n....

How can I do this?

Upvotes: 3

Views: 4782

Answers (2)

JNK
JNK

Reputation: 65147

You need to use IDENTITY_INSERT:

SET IDENTITY_INSERT MyTableName ON

INSERT INTO MyTableName
(IdField, <list of all the fields>)
VALUES
(999, <list of other values)

SET IDENTITY_INSERT MyTableName OFF

DBCC CHECKIDENT(MyTableName, RESEED, 2)

You also have to use the explicit field list in the INSERT statement, and are limited to one active IDENTITY_INSERT per session.

This will also reset the seed value to be whatever you inserted if it's higher than the current ID value.

You can get around this too by using DBCC CHECKIDENT but it'll be a big pain to manage.

Upvotes: 6

Remus Rusanu
Remus Rusanu

Reputation: 294217

You can use IDENTITY_INSERT:

SET IDENTITY_INSERT [tablename] ON;
INSERT INTO [tablename] (identitycolumn, ... ) VALUES (999, ...);
SET IDENTITY_INSERT [tablename] OFF;

Having to resort to this is always a code smell. It is onyl acceptable as a data load practice for table population, or for replicating data. For an app to 'reserve' IDs is a a bad practice and indicates a data model design flaw.

Upvotes: 6

Related Questions