CODEWITHSUNDEEP
sql-serverdatabase
Mathias
Mathias

Reputation: 34251

Force default value when adding column to table - SQL Server

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?

Upvotes: 5

Views: 11376

Answers (4)

sandeep rawat
sandeep rawat

Reputation: 4957

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

Upvotes: 0

chrisb
chrisb

Reputation: 2210

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

Upvotes: 1

Cade Roux
Cade Roux

Reputation: 89661

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.

Upvotes: 4

boes
boes

Reputation: 2855

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

Upvotes: 10

Related Questions