Add a column, with a default value, to an existing table in SQL Server


You can do it by an edit of table design if you want do it with query see following.

Syntax:

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

Note: Use WITH VALUES to update existing null-able rows.

Example:

ALTER TABLE MyTable
ADD MyTableTypeID INT NOT NULL
CONSTRAINT Constraint_MyTableTypeID DEFAULT 0
GO

Note: WITH VALUES handles the NOT NULL part

ALTER TABLE MyTable
ADD MyTableTypeID INT
CONSTRAINT Constraint_MyTableTypeID DEFAULT 0 WITH VALUE
GO



Related Posts

Fatal Error: Peer authentication failed for user "postgres", when trying to get pgsql working with rails.

Problem: Sometimes, we get the following error when trying to make a connection in Rails.  FATAL ERROR: Peer authentication failed for us...