January 17, 2004 at 10:34 am
If I have the need to create a new field for a table where records already exist, and that new field will have a DEFAULT applied to it, is there a way to populate the field for the existing records without having to run a seperate UPDATE statement? Thanks for your help! CSDunn
January 18, 2004 at 6:04 pm
According to Books on Line ...
DEFAULT
Is a keyword that specifies the default value for the column. DEFAULT definitions can be used to provide values for a new column in the existing rows of data. DEFAULT definitions cannot be added to columns that have a timestamp data type, an IDENTITY property, an existing DEFAULT definition, or a bound default. If the column has an existing default, the default must be dropped before the new default can be added. To maintain compatibility with earlier versions of SQL Server, it is possible to assign a constraint name to a DEFAULT.
... so the answer appears to be "yes".
Just to be sure, why not create a table, populate a few rows, and try it.
January 18, 2004 at 10:15 pm
The added new field has to be not null with default value.
January 19, 2004 at 6:05 am
ALTER TABLE Xtable ADD Xcolumn int NOT NULL DEFAULT (0) WITH VALUES
HTH
Sachin
Regards,
Sachin Dedhia
January 21, 2004 at 12:25 pm
'WITH VALUES' is what I was looking for! I had seen it before, but I could not locate this in BOL.
Thanks for your help!
CSDunn
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply