In SQL Server 2000/2005,
Is it possible to force the default value to be written to already existing rows when adding a new column to a table without using NOT NULL on the new column?
Add a comment
|
4 Answers
You need two statements. First create the column with not null. Then change the not null constraint to nullable
alter table mytable add mycolumn varchar(10) not null default ('a value')
alter table mytable alter column mycolumn varchar(10) null
Comments
I understand your question, but you are saying that for future records, NULL (unknown, indeterminate or whatever your semantics are) is acceptable (but if it is left off in an insert, there will be a default), but that for all the existing data, you are going to go ahead and assign it the default.
I would have to look hard at this situation and ask why you are even going to allow NULLs in future records at all - given none of the historical records will have it, and there is a default in place for future records.
Comments
I doubt it.
http://msdn.microsoft.com/en-us/library/ms190273(SQL.90).aspx
The approach recommended by Microsoft is as follows (taken from the url above)
UPDATE MyTable SET NullCol = N'some_value' WHERE NullCol IS NULL
ALTER TABLE MyTable ALTER COLUMN NullCOl NVARCHAR(20) NOT NULL
Comments
ALTER TABLE {TABLENAME}
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL}
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
[**WITH VALUES]**
WITH VALUES can be used to store the default value in the new column for each existing row in the table.
more detail on MSDN link . https://msdn.microsoft.com/en-in/library/ms190273.aspx
