Posted by Roy Harvey on 01/02/07 17:38

ALTER the table to add a new INT column. Set the value of that column
based on the data in the BIT column. Alter the table to drop the BIT
column. Rename the INT column.

Roy Harvey
Beacon Falls, CT

On 2 Jan 2007 09:19:00 -0800, teddysnips@hotmail.com wrote:

>A few weeks ago a client asked me to add a column to a table so I
>created this script:
>
>ALTER TABLE dbo.tblIndividual ADD fldRenewalStatus BIT NOT NULL
>CONSTRAINT fldRenewalStatus_Default DEFAULT 0
>
>Now they want to change it from a BIT to an INT, to store an enum.
>Fair enough. However, no matter how much I wrangle with a script, I
>can't find a reliable way to alter the column. I've mixed and matched
>the following and nothing seems to work:
>
>EXEC sp_unbindefault 'tblIndividual.fldRenewalStatus'
>
>DROP DEFAULT DF_tblIndividual_fldRenewalStatus
>
>ALTER TABLE tblIndividual
>DROP CONSTRAINT fldRenewalStatus_Default
>
>ALTER TABLE tblIndividual
>DROP COLUMN fldRenewalStatus
>GO
>
>ALTER TABLE tblIndividual
>ADD fldRenewalStatus int NOT NULL
>CONSTRAINT fldRenewalStatus_Default DEFAULT 0
>
>
>Thoughts?
>
>Thanks
>
>Edward

• Next in forum: Re: does ms sql support two phase commit protocol
• Prev in forum: Re: many tables vs one big table
• Thread view: Re: Changing a BIT to an INT where there's a CONSTRAINT and a DEFAULT

[Reply to this message]