1

I am trying to add a range or exclude numbers from an auto increment column in sql.

We have an auto increment column regarding Job Numbers since each Job is unique. The problem is we treat ourselves as a client as well, and the job number as 99999 where as the other job numbers end for example 10500. When the auto increment takes place, it follows on from 99999. This there a way to exclude this or do I have to manual set the IDENTITY_INSERT ON and insert the ID myself?

Thanks in advance

Quade du Toit
  • 33
  • 11
  • If you use `SET IDENTITY_INSERT ON` and insert a value of 99999, then the identity column will be using 100000 as its next value. You cannot insert "out of the sequence" values into an identity column without affecting the identity value itself.... – marc_s Mar 03 '16 at 09:35
  • so there is no way of setting a range to auto increment? – Quade du Toit Mar 03 '16 at 09:37
  • 1
    No - if you need to have ranges and things like that, you need to look into `SEQUENCE` objects which are available as of SQL Server 2012 and newer – marc_s Mar 03 '16 at 09:38
  • 1
    Or use a *negative value* e.g. `-1` for your "special" internal job - that wouldn't "ruin" your identity values! – marc_s Mar 03 '16 at 09:39
  • Thanks Marc_s. Will most defiantly investigate SQL SEQUENCE. Looks promising. – Quade du Toit Mar 03 '16 at 09:47

1 Answers1

0

You can set off the auto-increment in the "Microsoft SQL Server"
Check: SQL-How to Insert Row Without Auto incrementing a ID Column?
In this way you can set the ID's yourself.

  Set Identity_Insert [TableName] On
  -- --------------------------------------------
  Insert TableName (pkCol, [OtherColumns])
  Values(pkValue, [OtherValues])
  -- ---- Don't forget to turn it back off ------
  Set Identity_Insert [TableName] Off
Community
  • 1
  • 1
M. Suurland
  • 725
  • 12
  • 31
  • Not exactly what i wanted but will use this in the mean while. SQL SEQUENCE seems to be what i am after though. Seems more automated. – Quade du Toit Mar 03 '16 at 09:49