May 21, 2011 at 10:52 am
Hi,
I am trying to alter a column to make it identity but getting error message:
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'identity'.
Here is my code:
Create Table one(IdNo int not null,Name Varchar(20))
Alter table one
Alter Column IdNo int Identity(3,1)
May 21, 2011 at 11:08 am
Tiya (5/21/2011)
Hi,I am trying to alter a column to make it identity but getting error message:
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'identity'.
Here is my code:
Create Table one(IdNo int not null,Name Varchar(20))
Alter table one
Alter Column IdNo int Identity(3,1)
Nope you can't alter the existing columns for identity column.
If you want create another one table and insert these values with identity_insert on
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
May 21, 2011 at 11:10 am
Please refer to the following - FYI:
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 21, 2011 at 11:11 am
is there no way to do alter???....is is bug in sql server?"
May 21, 2011 at 11:15 am
Tiya (5/21/2011)
is there no way to do alter???....is is bug in sql server?"
No as was stated you can't alter an existing column to make it an identity column but you can create a new table, load it and drop the original table.
Please refer to the article that I posted.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 21, 2011 at 11:19 am
okay....i try this..thanks for replying
May 21, 2011 at 12:18 pm
One option to add the identity property would be using SSMS (right-click on the table -> design).
"Under the hood" it creates a new table, insert the original data, drop the old table and rename the new one.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply