CODEWITHSUNDEEP
sqlsql-serveralter-table
Nate Pet
Nate Pet

Reputation: 46322

SQL Server: ALTER with ADD new column

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?

Upvotes: 9

Views: 23242

Answers (2)

Chiragkumar Thakar
Chiragkumar Thakar

Reputation: 3726

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)

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 453807

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?

Upvotes: 19

Related Questions