79

I have a table Orders with the following fields:

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, for example, 9586.

I want to have the autoincrement field start from that value.

How can I do this?

Tony
  • 9,672
  • 3
  • 47
  • 75
MartinHN
  • 19,542
  • 19
  • 89
  • 131

4 Answers4

153

From Resetting SQL Server Identity Columns:

Retrieving the identity for the table Employees: 

DBCC checkident ('Employees')

Repairing the identity seed (if for some reason the database is inserting duplicate identities):

DBCC checkident ('Employees', reseed)

Changing the identity seed for the table Employees to 1000:

DBCC checkident ('Employees', reseed, 1000)

The next row inserted will begin at 1001.

Michael Myers
  • 188,989
  • 46
  • 291
  • 292
jengar
  • 1,636
  • 1
  • 10
  • 3
29

You need to set the Identity seed to that value:

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

To alter an existing table:

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

More info on CREATE TABLE (Transact-SQL) IDENTITY (Property)

MIWMIB
  • 1,407
  • 1
  • 14
  • 24
Nick Clarke
  • 1,262
  • 1
  • 10
  • 13
  • 13
    The ALTER TABLE command above does not work for me in Microsoft SQL Server 2012. It gives this error: "Incorrect syntax near the keyword 'IDENTITY'." The DBCC command in the accepted answer worked correctly though. – Eric Barr Jul 28 '17 at 22:40
11

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.

Pang
  • 9,564
  • 146
  • 81
  • 122
Paul Williams
  • 16,585
  • 5
  • 47
  • 82
  • just what I was after - was useful for populating a database that I was migrating from a mysql php to a C# MVC app – pigeon Nov 11 '15 at 05:42
3

In the Table Designer on SQL Server Management Studio you can set the where the auto increment will start. Right-click on the table in Object Explorer and choose Design, then go to the Column Properties for the relevant column:

Here the autoincrement will start at 760

Razvan Socol
  • 5,426
  • 2
  • 20
  • 32
Try In Incognito
  • 51
  • 3