how to turn an INT IDENTITY(1,1) into an INT... ???

  • The current flow has me adding a non-identity temp column, copying the data, dropping the identity col, then sp_renaming the temp to the real name.

    it works, but I have to do a bunch of index and constraint work that takes a lot of time.

    Also, the current identity column is the first in the table definition, and with my flow, the non-identity will be the last.  I'm afraid this is going to throw off some of my Crystal Report guys who assume column order is set in stone.

    a command such as

    alter table dim_customer alter column cust_id int not null

    will succeed, but won't drop the identity attribute.

    Anyone?  Anyone?  Bueller?  Bueller?

    THANKS

  • Because the only "correct" way of doing it is exactly what you described and you will be having some hard time with the scripts to do it, the safest way I have found to do it is with EM. The tool will do the job for you

    Cheers,

     


    * Noel

  • How about just turning Identity_Insert on for the column in question? In that case it will behave just like ordinary int, with additional benefit of having default value of identity?

  • Pretty sure you can only have one table at a time with IDENTITY_INSERT ON. I'm not in front of QA right now... can anyone confirm?

  • Yes, you are correct.  Only one table at a time in a session, and only for the life of the session.  So it is certainly not the permanent solution that you are looking for.

    BTW, even if you were not in front of QA, you were in a browser (must have to read the reply), so you could have googled IDENTITY_INSERT, which would take your right to MSDN library with the description of the command.



    Mark

  • There's no proof like pudding Mark.

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

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