Set start value for column with autoincrement

Question

Hi

I'm using MS Sql Server.

I have a table like this:

[Orders]
Id | SubTotal | Tax | Shipping | DateCreated

The Id column is set to autoincrement(1,1).

This is to be used in an E-commerce storefront. Sometimes a current E-commerce store is migrated to my platform and they already have Orders - which could mean that their current Order Id is e.g. 9586.

Then I want to have autoincrement start from that value.

How can I do this?

Answer 1

You need to set the Identity seed to that value:

CREATE TABLE orders
(
 id int IDENTITY(9586,1)
)

To alter a current table:

ALTER TABLE orders ALTER COLUMN Id IDENTITY (9586, 1);

More info on MSDN - IDENTITY (Property)

Answer 2

From Resetting SQL Server Identity Columns:

Retrieving:

DBCC checkident ('Employees')

Repairing the Identity Seed

DBCC checkident ('Employees', reseed)

Changing the Identity Seed

DBCC checkident ('Employees', reseed, 1000)

Answer 3

Also note that you cannot normally set a value for an IDENTITY column. You can, however, specify the identity of rows if you set IDENTITY_INSERT to ON for your table. For example:

SET IDENTITY_INSERT Orders ON

-- do inserts here

SET IDENTITY_INSERT Orders OFF

This insert will reset the identity to the last inserted value. From MSDN:

    If the value inserted is larger than the current identity value for the table, SQL Server automatically uses the new inserted value as the current identity value.