July 19, 2006 at 9:13 am
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
July 19, 2006 at 12:51 pm
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
July 20, 2006 at 5:08 am
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?
July 20, 2006 at 7:22 am
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?
July 20, 2006 at 7:29 am
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
July 20, 2006 at 8:43 am
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