SQL Server: ALTER with ADD new column

Question

I have the following statement in SQL Server:

ALTER TABLE [dbo].[TruckTbl] 
ADD [TruckState] [bit] NULL DEFAULT 0;

What this will do is to default any NEW record to 0.

For existing records how can I get and say in the statement also default those to 0.

I know I can go and do an update but anyway to do within an update in the ALTER statement above?

Answer 1

Use the WITH VALUES clause

 ALTER TABLE [dbo].[TruckTbl] ADD [TruckState] [bit] NULL DEFAULT 0 WITH VALUES;

Although I do agree with the other answer it seems odd that the column should be nullable at all if you are setting all existing rows to 0 and have a default for future inserts. Do you ever need to allow NULL as a column value here?

Answer 2

ALTER TABLE {TABLENAME} 
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL} 
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
[WITH VALUES]

E.g

ALTER TABLE Temp
ADD ID int NOT NULL DEFAULT(1)