CODEWITHSUNDEEP
sqlsql-server
sesame
sesame

Reputation: 825

how to change identity increment of column

Following statement can reset seed

DBCC CHECKIDENT ('TableName', RESEED, 1)

but this time I have to change its increment .

Upvotes: 4

Views: 16174

Answers (4)

dunwan
dunwan

Reputation: 1607

You can reset the auto increment to a higher value by

  1. Set IDENTITY_INSERT off
  2. Running an insert statement with ID set to where you want to continue from
  3. Set IDENTITY_INSERT on
  4. Delete the row created (assuming it was only done to increment seed)

For example;

Table 'People' with last ID = 10 can be set to continue from 400,000 by;

SET IDENTITY_INSERT AccessToken off
insert into People(Id, name) values (400000,'Bob')
SET IDENTITY_INSERT AccessToken on
Delete from People where ID = 400000

Upvotes: 0

KPrasad
KPrasad

Reputation: 1

We can't update a column's identity to increment by 2 on each entry. The easiest method is to create another table with IDENTITY(1,2) and move the data to that table before dropping the actual table. Please go through the script below.

Let TableA is our actual table.

  1. CREATE TABLE TableB(col1 INT IDENTITY (1,2) NOT NULL, col2 VARCHAR(10) NULL);
  2. INSERT INTO TableB SELECT col2 FROM TableA;
  3. DROP TABLE TableA;
  4. sp_rename TableB, TableA;

Upvotes: 0

Aleris
Aleris

Reputation: 8059

Or you can use Sql Server Management Studio:

Identity Increment in Sql Server Management Studio

Using this approach will most likely recreate the table.

Upvotes: 1

petergo
petergo

Reputation: 390

Hope this helps

 ALTER TABLE MyCustomers 
 ALTER COLUMN CustId IDENTITY (200, 2)

Code from ms-help://MS.VSCC.v90/MS.MSDNQTR.v90.en/ssmprog3/html/5719d3e4-14db-4073-bed7-d08f39416a39.htm

Upvotes: 0

Related Questions