• support@answerspoint.com

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

972

How can a column with a default value be added to an existing table in SQL Server 2000 / SQL Server 2005?

6Answer


0
ALTER TABLE {TABLENAME} 
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL} 
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
[WITH VALUES]
  • answered 2 years ago
  • G John

0
ALTER TABLE Protocols
ADD ProtocolTypeID int NOT NULL DEFAULT(1)
GO
  • answered 2 years ago
  • Gul Hafiz

0

WITH VALUES handles the NOT NULL part...

ALTER TABLE table
ADD column BIT 
CONSTRAINT Constraint_name DEFAULT 0 WITH VALUES
  • answered 2 years ago
  • G John

0
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 2 years ago
  • G John

0

The most basic version with two lines only

ALTER TABLE MyTable
ADD MyNewColumn INT NOT NULL DEFAULT 0
  • answered 2 years ago
  • B Butts

0

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 2 years ago
  • Gul Hafiz

Your Answer

    Facebook Share