2

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?

JNK
  • 63,321
  • 15
  • 122
  • 138
Achilles
  • 11,165
  • 9
  • 62
  • 113
  • As a side note, you'd need to be careful when you insert row 998; as the next insert will violate the PK constraint (since 999 is already in there) – Jim B Apr 18 '12 at 17:09
  • Thanks Jim B, the assumption is that this table is sufficiently small enough that it will never reach the reserved number. – Achilles Apr 18 '12 at 17:23
  • No problem. We had something similar like this here; where we decided that 1-499 would be reserved, so the identity seed started at 500. not sure if that'll help in your scenario. – Jim B Apr 18 '12 at 17:42

2 Answers2

6

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.

Achilles
  • 11,165
  • 9
  • 62
  • 113
JNK
  • 63,321
  • 15
  • 122
  • 138
5

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.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • Remu are you the service broker guru from rusanu consulting? If you are your dialog pooling techniques really opened my eyes to the power of service broker. – Namphibian Apr 18 '12 at 17:09