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 · 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

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

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

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

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

Examples related to sql-server