Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 362
  • Last Modified:

adjust autonumber

I have a sql database that has an autonumber and the ID column looks like this

1
2
3
4
5
6
7
.....


Can I change the current numbers so that all the CURRENT numbers in the table begin from 100000 along with any new numbers after that?

100001
100002
100003
100004
100005
100006
100007
.........
0
al4629740
Asked:
al4629740
2 Solutions
 
Ryan ChongCommented:
Try open the table in Design mode in your Management Studio, then select the field, under the Table Designer, select Identity Specification, set the Identity Seed to 100000.
0
 
radcaesarCommented:
CREATE TABLE sales

(sale_id INT IDENTITY(100000,1)

If it exists already, use "DBCC CHECKIDENT" with RESEED keyword.
0
 
al4629740Author Commented:
Like this??

DBCC CHECKIDENT (tablename, RESEED, 100000,1)
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
al4629740Author Commented:
Will that change the current values also??
0
 
al4629740Author Commented:
I have a current table.

Can I create a new ID column that  starts from 100000, without deleting the records?

0
 
Ryan ChongCommented:
@al4629740

you can try the method I had suggested.
0
 
al4629740Author Commented:
That section is greyed out and I can't change the seed number using your method
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
>Can I create a new ID column that  starts from 100000, without deleting the records?


in order to do that, you can drop your identity column, and add a column with identity (100000,1)
alter table tablename add iid int identity (100000,1 )
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now