Adding columns using BIT datatype to an existing table

[krussman]

I'm trying to add a column of BIT datatype to an existing table. The BIT datatype is not in the datatype dropdown box to select, but I know it's available because other tables in my database have fields of BIT datatype.<br>
<br>
I also cannot use the ALTER TABLE procedure to add a column of BIT datatype because the procedure requires the new column to allow NULL values (which BIT datatypes do not).<br>
<br>
What else can I do?<br>
<br>
TIA,<br>
Ken Russman

 

[Natraj]

Hi Ken,<br>
You are right, the table cannot be altered to have a null bit datatype either by using enterprise manager or by using ALTER TABLE procedure.<br>
But there is a way out, Generate the SQL script for the table and modify it to add the bit column with null and create a new table, now import the data from the old table into new table. Drop the old table if you want.<br>
Try this, hope it works for you.<br>
Natraj

 

[harryrich]

Since I just did this yesterday in 7.0, service pack 3, I'm pretty sure it works:

(1) Working from the Enterprise manager open the table design grid and add the field. You will have to scroll UP to get to the BIT datatype since it is alphabetically earlier than the default, CHAR.

(2) Make sure the Allow Nulls box is checked. Save the new design.

(2) Fill the column with values.

(3) If you wish to prohibit null values, open the table design again and uncheck the Allow Nulls box. Save the final design.