Вопрос: Добавьте столбец со значением по умолчанию в существующую таблицу в SQL Server


Как добавить столбец со значением по умолчанию в существующую таблицу в SQL Server 2000 / SQL Server 2005 ?


2264


источник


Ответы:


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

2882



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

Включение ПО УМОЛЧАНИЮ заполняет столбец в существующий строки со значением по умолчанию, поэтому ограничение NOT NULL не нарушается.


857



При добавлении нулевой столбец , WITH VALUESбудет гарантировать, что к существующим строкам будет применено определенное значение DEFAULT:

ALTER TABLE table
ADD column BIT     -- Demonstration with NULL-able column added
CONSTRAINT Constraint_name DEFAULT 0 WITH VALUES

192



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

112



ALTER TABLE MYTABLE ADD MYNEWCOLUMN VARCHAR(200) DEFAULT 'SNUGGLES'

102



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.


80



The most basic version with two lines only

ALTER TABLE MyTable
ADD MyNewColumn INT NOT NULL DEFAULT 0

75



Use:

-- Add a column with a default DateTime  
-- to capture when each record is added.

ALTER TABLE myTableName  
ADD RecordAddedDate smalldatetime NULL DEFAULT(GetDate())  
GO 

63



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

55



Use:

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

Reference: ALTER TABLE (Transact-SQL) (MSDN)


44



You can do the thing with T-SQL in the following way.

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

As well as you can use SQL Server Management Studio also by right clicking table in the Design menu, setting the default value to table.

And furthermore, if you want to add the same column (if it does not exists) to all tables in database, then use:

 USE AdventureWorks;
 EXEC sp_msforeachtable
'PRINT ''ALTER TABLE ? ADD Date_Created DATETIME DEFAULT GETDATE();''' ;

41



In SQL Server 2008-R2, I go to the design mode - in a test database - and add my two columns using the designer and made the settings with the GUI, and then the infamous Right-Click gives the option "Generate Change Script"!

Bang up pops a little window with, you guessed it, the properly formatted guaranteed-to-work change script. Hit the easy button.


41