alter query problem

Question

Post reply

alter query problem

ranganathanmca

SSC Eights!

Points: 920
More actions
September 5, 2009 at 3:19 am

#95990

hi expects
i want to alter the table existing column to int to identity, using alter query it's showing error for
ERROR :
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'identity'.
Query :
alter table person alter column id int not null identity(1,1);
(other)
alter table person alter column id int identity(1,1);
thanks for advance

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ps. One Orange Chip Points: 29422 More actions · Answer 1

you can modify the table's property from management studio.

Right click on your table, Modify, from the column properties tab, you can modify the column to be identity.

Pradeep Singh

ranganathanmca SSC Eights! Points: 920 More actions · Answer 2

thanks for your reply ps

i want do it on sql query analyzer only if it possible for this plz tell me what is the error and solution

thanks for advance

Lowell SSC Guru Points: 323518 More actions · Answer 3

ranganathanmca (9/5/2009)
thanks for your reply ps
i want do it on sql query analyzer only if it possible for this plz tell me what is the error and solution
thanks for advance

Paradeep was pointing out the easiest way to add your identity.

once a table is built, you cannot alter an existing column to have the identity property; you can add a New column with identity to a table, though.

With That in mind,there's two ways to do it, then:

1: create a duplicate table with the identity, transfer the data from the original to the new, drop the original, and rename the new table to the original name.

2. add a new column with identity,drop the old column, rename the new column, but lose the original values from the old column

this can get incredibly complex if the table you are fiddling with has foreign keys ties to it, not to mention check constraints, default constraints, etc. Much easier to let the GUI do it.

the GUI does all of that grunt work for you.

here's an example of doing it with all TSQL, on a table without all the headache problems I mentioned:

create table example(exampleid int not null

,exampletext varchar(30) )

--fails! you cannot add identity as part of an alter statement.

--you have to rebuild the table

alter table example alter column exampleid int identity(1,1) not null

create table example2(exampleid int identity(1,1) not null primary key

,exampletext varchar(30) )

set identity_insert example2 ON

INSERT INTO example2(exampleid,exampletext)

select exampleid,exampletext from example

set identity_insert example2 OFF

drop table example

sp_rename example2,example

here's an example

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ps. One Orange Chip Points: 29422 More actions · Answer 4

I dont think it's possible to modify a column to take identity property.

Please have a look at the following article from SSC, which describes two ways of achieving what you're doing. Remember, the position of the identity column will change in the table structure. If you application is calling the columns by ordinal position of the columns, there will be error; if it's referring by column's name, it'll work fine(in the 1st example of the article), 2nd example will work fine though.

http://www.sqlservercentral.com/articles/T-SQL/61979/

Pradeep Singh

ps. One Orange Chip Points: 29422 More actions · Answer 5

You were much faster Lowell with detailed explanation.:w00t:

Pradeep Singh