Add a column, with a default value, to an existing table in SQL Server
How can a column with a default value be added to an existing table in SQL Server 2000 / SQL Server 2005?
sql
sql-server
- asked 8 years ago
- G John
6Answer
ALTER TABLE {TABLENAME}
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL}
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
[WITH VALUES]
- answered 8 years ago
- G John
ALTER TABLE Protocols
ADD ProtocolTypeID int NOT NULL DEFAULT(1)
GO
- answered 8 years ago
- Gul Hafiz
WITH VALUES handles the NOT NULL part...
ALTER TABLE table
ADD column BIT
CONSTRAINT Constraint_name DEFAULT 0 WITH VALUES
- answered 8 years ago
- G John
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
- answered 8 years ago
- G John
The most basic version with two lines only
ALTER TABLE MyTable
ADD MyNewColumn INT NOT NULL DEFAULT 0
- answered 8 years ago
- B Butts
If you want to add multiple columns you can do it this way for example:
ALTER TABLE YourTable
ADD Column1 INT NOT NULL DEFAULT 0,
Column2 INT NOT NULL DEFAULT 1,
Column3 VARCHAR(50) DEFAULT 'Hello'
GO
- answered 8 years ago
- Gul Hafiz
Your Answer