alter column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

alter column

Author

Topic

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-09-05 : 09:31:25

Dear All,
i've table test with columns ID int, name varchar(40).
i'd like to give the autoincrement values from 7000. i'm getting error with the below query. please correct me. data is there in the table

alter table test1 alter column id int identity(7000,1)

Arnav
Even you learn 1%, Learn it with 100% confidence.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-05 : 09:34:47

It is not possible using ALTER command. Do this via SSMS for easiness

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-07 : 00:05:16

or do this work around
1. create a new colun id2

alter table test1 add  id2 int identity(7000,1)

2.copy current values to new column

set identity_insert test1 on 
Update test1
set id2=id
set identity_insert test1 off

3. drop the old column and rename new column

alter table test1 drop column id
go

EXEC sp_rename 'test1.[id2]', 'id', 'COLUMN'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources