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

sql, sql-server

Solution

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.

Problem

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?

Original source