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

Question

How do you add a column, with a default value, to an existing table in SQL Server 2000/2005?

Answer 1

ALTER TABLE ADD ColumnName Options

Here is a link that has all of the alter table syntax: http://doc.ddart.net/mssql/sql70/aa-az_5.htm

EDIT: Accidentally hit submit. And markdown ate my brackets.

Answer 2

ALTER TABLE Protocols
ADD ProtocolTypeID int NOT NULL DEFAULT(1)
GO

Answer 3

ALTER TABLE {TABLENAME} 
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL} 
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}

Answer 4

ALTER TABLE <table name> 
ADD <new column name> <data type> NOT NULL
GO
ALTER TABLE <table name> 
ADD CONSTRAINT <constraint name> DEFAULT <default value> FOR <new column name>
GO

Answer 5

Mainly using SQL Server Enterprise Manager - it's simple. :D

Answer 6

Beware when the column you are adding has a NOT NULL constraint, yet does not have a DEFAULT constraint (value). The ALTER TABLE statement will fail in that case if the table has any rows in it. The solution is to either remove the NOT NULL constraint from the new column, or provide a DEFAULT constraint for it.

Answer 7

i have already added a column in the table and now want to get the data in that column from another table in another database. can i do this? if yes then how?

Answer 8

That one has to work.

UPDATE dbo.YourTable 
SET dbo.YourTable.NewColumn = SourceServer.SourceDB.dbo.SourceTable.Column
FROM dbo.YourTable as y
    INNER JOIN SourceServer.SourceDB.dbo.SourceTable.Column as s 
    ON (y.ColumnID = s.ColumnID);
GO

Shure you have to set up a linked server if your source database is on the another SQL Server instance. And possible you need unique constraint or primery key on ColumnID.

More info here: http://msdn.microsoft.com/en-us/library/ms177523.aspx