Null to Not Null
-
Hi!
I'm a newbie working with SQL Server (2000) and need some assistance in updating several float fields in a table. Currently, the fields have no default value and allow nulls. I want to alter the table so nulls are not allowed and set a default value as 0.
There are records in the table that may/may not have nulls in the fields. What is the best way to handle this, without blowing away the data or the table? The SQL Server doco says to add a new field and copy the data to it, then blow away the old field. Isn't there an easier way of handling this?
Thanks,
Linda Boumarafi
*********************
GTN Inc.
*********************
-
You have to drop and recreate the table. You can make a new table, move the data, and then rename it (after dropping the old table). This is what will happen in Enterprise Manager if you make the change there.
Make the change in the table designer and then click the "Script" button to see what will run. This is the button that has the save disk with a scroll image on it.
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com -
Update the data so there are no NULLs in the table
update table set floatcol=0 where floatcol is null
Then use an alter statement to remove change the NULLABILITY
alter table <your table> alter column floatcol float not null
Then add a DEFAULT constraint to the table.
Not 100% sure of the syntax:
alter table with nocheck add DEFAULT df_table_floatcol = 0
This keeps your table intact.
-
Here is some code to change the not null and default value for a column:
create table xxx(xxx char(1) not null, yyy char(1))
insert into xxx (xxx) values ('x')
select * from xxx
update xxx
set yyy = 0
where yyy is null
alter table xxx
alter column yyy char(1) not null
ALTER TABLE xxx WITH NOCHECK ADD
CONSTRAINT df_yyy DEFAULT ('0') FOR yyy
insert into xxx (xxx) values ('x')
select * from xxx
drop table xxx
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
-
Thank you everyone who responded. Earlier today before posting, I managed to blow away first the data and then the table in the test database!
I was concerned that accomplishing this task would be involved. Based on your comments, I managed to accomplish the task by scripting the updates to set the fields to zero and modifying the table to not allow nulls, followed by a script to add the constraints. This time all went according to plan!
Linda
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply