Alter table command - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Alter table command

Author

Topic

vmurali
Yak Posting Veteran

88 Posts

Posted - 2008-11-06 : 00:38:13

Hi,
I have table Types with columns type_id,type_name.

Type_id int not null
Type_name varchar(50) not null

I want to change the type_id into a identity field. when I am trying to write the below alter statement I am getting error

ALTER TABLE Types
MODIFY type_id int identity(1,1)

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'MODIFY'.

Can anyone help me in solving this problem.

SimpleSQL
Yak Posting Veteran

85 Posts

Posted - 2008-11-06 : 00:53:35

ALTER TABLE Types
ALTER COLUMN type_id int identity(1,1)

BOL is your friend :)

vmurali
Yak Posting Veteran

88 Posts

Posted - 2008-11-06 : 01:23:55

Thanks for the reply. But I am still getting error

"Incorrect syntax near the keyword 'identity'."

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-11-06 : 01:50:47

U cant change a column to Identity with alter table stmt.
First drop the existing column and then recreate it with identity


ALTER TABLE Types DROP COLUMN type_id
ALTER TABLE Types ADD type_id INT IDENTITY(1,1)[/CODE]

[code]"There is only one difference between a dream and an aim. A dream requires soundless sleep to see, whereas an aim requires sleepless efforts to achieve..!!"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-06 : 02:38:42

quote:
Originally posted by PeterNeo

U cant change a column to Identity with alter table stmt.
First drop the existing column and then recreate it with identity

ALTER TABLE Types DROP COLUMN type_id
ALTER TABLE Types ADD type_id INT IDENTITY(1,1)[/CODE]

[code]"There is only one difference between a dream and an aim. A dream requires soundless sleep to see, whereas an aim requires sleepless efforts to achieve..!!"

Make sure you also script out any constriants on it before dropping and reapply after creating the new column.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources