CODEWITHSUNDEEP
sql-server
MartinHN
MartinHN

Reputation: 19772

Set start value for column with autoincrement

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?

Upvotes: 83

Views: 162406

Answers (4)

Try In Incognito
Try In Incognito

Reputation: 61

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

Upvotes: 3

Nick Clarke
Nick Clarke

Reputation: 1282

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)

Upvotes: 31

Paul Williams
Paul Williams

Reputation: 17020

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.

Upvotes: 12

jengar
jengar

Reputation: 1716

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.

Upvotes: 161

Related Questions