alter table and removing IDENTITY property

  • ckempste

    SSCoach

    Points: 17983

    Hi all

    Im having some trouble working out the syntax to alter a table column and remove the IDENTITY property from it. I have a large data move coming up and need to "turn off" identity of some of the tables, insert the data, and reapply the identity after the load.

    Ideas?

    Cheers

    Chris


    Chris Kempster
    www.chriskempster.com
    Author of "SQL Server Backup, Recovery & Troubleshooting"
    Author of "SQL Server 2k for the Oracle DBA"

  • Chris Hedgate

    One Orange Chip

    Points: 25041

    SET IDENTITY_INSERT tablename ON

    INSERT INTO tablename .......

    SET IDENTITY_INSERT tablename OFF

    Note that only one table at a time (per session) can have IDENTITY_INSERT on.

    Chris Hedgate @ Apptus Technologies (http://www.apptus.se)

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715835

    Be sure when you do this, you supply a specific identity value.

    Steve Jones

    steve@dkranch.net

  • ckempste

    SSCoach

    Points: 17983

    Hi guys

    Hmmm.. yeh, knew this one and wasnt what I was after . Basically I wanted to permanently remove IDENTITY from a column via an ALTER DATABASE statement. Is this only possible via dropping the table all together?

    Cheers

    Chris


    Chris Kempster
    www.chriskempster.com
    Author of "SQL Server Backup, Recovery & Troubleshooting"
    Author of "SQL Server 2k for the Oracle DBA"

  • Andy Warren

    SSC Guru

    Points: 119676

    Hey, I just profiled it a couple times, then changed the identity setting via EM several times - looks like it does drop the table each time, but at least you don't have to do the work! Possibly because its late Im just not seeing it, but the only alter I see are those performed on the "new" table to apply constraints to it. That sorta makes sense since identity is treated like a constraint, but where is the "drop identity" or whatever?

    Anyone have any insight to offer?

    Andy

  • ckempste

    SSCoach

    Points: 17983

    Andy

    Hey there Andy, check this out from profiler...my table "aaaaa" didnt have identity enabled, then enabled it and got this:

    CREATE TABLE dbo.Tmp_aaaaa

    (

    aaa int NOT NULL IDENTITY (1, 1),

    bbb char(10) NULL

    ) ON [PRIMARY]

    go

    SET IDENTITY_INSERT dbo.Tmp_aaaaa ON

    go

    IF EXISTS(SELECT * FROM dbo.aaaaa)

    EXEC('INSERT INTO dbo.Tmp_aaaaa (aaa, bbb)

    SELECT aaa, bbb FROM dbo.aaaaa TABLOCKX')

    go

    SET IDENTITY_INSERT dbo.Tmp_aaaaa OFF

    DROP TABLE dbo.aaaaa

    exec sp_cursoropen @P1 output, N'EXECUTE sp_rename N''dbo.Tmp_aaaaa'', N''aaaaa'', ''OBJECT''

    go


    Chris Kempster
    www.chriskempster.com
    Author of "SQL Server Backup, Recovery & Troubleshooting"
    Author of "SQL Server 2k for the Oracle DBA"

  • Andy Warren

    SSC Guru

    Points: 119676

  • ckempste

    SSCoach

    Points: 17983

    Yep.


    Chris Kempster
    www.chriskempster.com
    Author of "SQL Server Backup, Recovery & Troubleshooting"
    Author of "SQL Server 2k for the Oracle DBA"

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715835

    If you make the change in EM and then click the "script" button (3rd from left) you should see this. Many things you do in EM are only possible through dropping a table and this is usually how EM scripts it.

    Steve Jones

    steve@dkranch.net

  • This was removed by the editor as SPAM

Viewing 10 posts - 1 through 10 (of 10 total)

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