Set start value for column with autoincrement

Question

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

User · Accepted Answer

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.

User · Answer

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

User · Answer

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

User · Answer

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

[sql-server] Set start value for column with autoincrement

Examples related to sql-server