1

I am using Microsoft SQL Server 2008. In database I have a table with an auto increment field (ID). I already have two rows inserted in the table, so the ID of the next row will be 3. What I want to do is that when the next row will be inserted, the value of it's ID to be for example 100. Is there any way to do this? (do not tell me to insert and delete 97 rows)

If no, then I could try to recreate the table, but how to set the value of ID to some defined value initially i.e. I want it to begin from 100, not from 1?

user1080381
  • 1,597
  • 1
  • 16
  • 22
  • 1
    Possible duplicate: http://stackoverflow.com/questions/1280705/set-start-value-for-column-with-autoincrement – Kamil Apr 27 '13 at 14:23

2 Answers2

4

Yes of course:

SET IDENTITY_INSERT dbo.YourTable ON

INSERT INTO dbo.YourTAble(AutoIncID, ...other columns....)
VALUES(100, ......)

SET IDENTITY_INSERT dbo.YourTable OFF

By using the SET IDENTITY_INSERT ... ON you enable the explicit insertion of values into an IDENTITY column. Don't forget to turn that off, though! And in your connection to SQL Server, only one table at a time can have this setting ON

Also: once you've done this, now your current IDENTITY value will be 100 - so the next row you insert in a normal fashion (without SET IDENTITY_INSERT ON active) will get the identity value of 101, then 102 and so on

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
2

or you could do:

dbcc checkident ( "YourTable", RESEED, 99 )

This will have the next insert go in with an id of 100

Brian Rice
  • 3,107
  • 1
  • 35
  • 53