SQL Server: ALTER with ADD new column

sql sql-server alter-table
Nate Pet picture Nate Pet · Feb 17, 2012 · Viewed 23.1k times · Source

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

Martin Smith picture Martin Smith · Feb 17, 2012

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?

Related questions

Rename column SQL Server 2008

I am using SQL Server 2008 and Navicat. I need to rename a column in a table using SQL. ALTER TABLE table_name RENAME COLUMN old_name to new_name; This statement doesn't work.

Read More
Adding an identity to an existing column

I need to change the primary key of a table to an identity column, and there's already a number of rows in table. I've got a script to clean up the IDs to ensure they're sequential starting at 1, works fine …

Read More
Altering column size in SQL Server

How to change the column size of the salary column in the employee table from numeric(18,0) to numeric(22,5)

Read More