Post reply

Removing Identy using T-SQL

  • January 19, 2004 at 5:52 am

    #84013

    There is a table with an identity column created and it might or might not have data. I need to remove this identity from that column. how do i do it with T-SQL?

    Can anyone help??

    Thanks

  • January 19, 2004 at 6:43 am

    #490649

    You cannot remove the identity property from a column.  You can disable the property using SET IDENTITY INSERT.



    --Jonathan

  • January 19, 2004 at 6:47 am

    #490650

    Thanks Jonathan,

    But i removed the identity using ALTER TABLE ALTER COLUMS statement, by redifining the column without the identity

    Cheers

    Vikram:-)

  • January 19, 2004 at 6:56 am

    #490652

    Are you sure?  What is the eaxct statement you used?  I see no support for this in the ALTER TABLE ALTER COLUMN syntax, and this certainly doesn't remove the property:

    use tempdb

    go

    create table test(id int identity)

    alter table test alter column id int

    sp_help test



    --Jonathan

  • January 19, 2004 at 9:30 am

    #490696

    Hi Jonathan

    you were right. I cannot remove the identity. I need to drop and recreate the table without the identity.

    Thanks

  • January 19, 2004 at 12:11 pm

    #490710

    Actually, why dropping the table?

    What about adding an int column, update that new column with the values of your 'identity' column, drop that identity column, and rename the int column?

    Well, okay, dropping might seem easier

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

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

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