Post reply

alter query problem

  • 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

  • September 5, 2009 at 4:06 am

    #1049707

    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

  • September 5, 2009 at 4:40 am

    #1049715

    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

  • September 5, 2009 at 5:37 am

    #1049720

    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!

  • September 5, 2009 at 5:38 am

    #1049721

    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://qa.sqlservercentral.com/articles/T-SQL/61979/


    Pradeep Singh

  • September 5, 2009 at 5:43 am

    #1049722

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


    Pradeep Singh

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

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