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

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

18.09.2008 12:30:04
и этот также w3schools.com/sql/sql_alter.asp
Developer 26.07.2016 23:10:26
30 ОТВЕТОВ
РЕШЕНИЕ

Синтаксис:

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

Пример:

ALTER TABLE SomeTable
        ADD SomeCol Bit NULL --Or NOT NULL.
 CONSTRAINT D_SomeTable_SomeCol --When Omitted a Default-Constraint Name is autogenerated.
    DEFAULT (0)--Optional Default-Constraint.
WITH VALUES --Add if Column is Nullable and you want the Default Value for Existing Records.

Заметки:

Имя необязательного ограничения:
если вы не укажете, CONSTRAINT D_SomeTable_SomeColSQL Server автоматически сгенерирует
    ограничение по умолчанию с забавным именем, например:DF__SomeTa__SomeC__4FB7FEF6

Дополнительное С-Values Заявление: только необходимо , когда ваша колонка обнуляемая     и вы хотите по умолчанию значения , используемым для существующих записей. Если ваш столбец есть , он будет автоматически использовать значение     по умолчанию для всех существующих записей, независимо от того, указали вы это или нет.
WITH VALUES

NOT NULL
WITH VALUES

Как вставки работают с ограничением по умолчанию:
если вы вставляете запись SomeTableи не указываете SomeColзначение, то по умолчанию оно будет 0.
Если вы вставите значение «Запись и указать SomeCol» как NULL(а ваш столбец допускает пустые значения),
    то ограничение по умолчанию не будет использоваться и NULLбудет вставлено как значение.

Примечания были основаны на всех замечательных отзывах ниже.
Особая благодарность:
    @Yatrix, @WalterStabosz, @YahooSerious и @StackMan за их комментарии.

3466
3.07.2018 20:44:15
Имейте в виду, что если столбец может иметь значение NULL, то значение NULL будет использоваться для существующих строк.
Richard Collette 31.01.2012 15:43:28
@Thecrocodilehunter Столбец Nullable означает, что вы можете вставить Null для значения столбцов. Если это не обнуляемый столбец, вы должны вставить какое-то значение этого типа данных. Таким образом, для существующих записей в них будет вставлено значение Null, а в новые записи будет добавлено значение по умолчанию, если не указано иное. Есть смысл?
Yatrix 29.02.2012 16:42:14
Мне нравится этот ответ немного лучше, чем dbugger, потому что он явно называет ограничение по умолчанию. Ограничение по умолчанию все еще создается с использованием синтаксиса dbugger, за исключением того, что его имя генерируется автоматически. Знание точного имени удобно при написании сценариев DROP-CREATE.
Walter Stabosz 23.03.2012 12:43:19
@ Vertigo Это верно только для столбца NOT NULL. Пожалуйста, попробуйте это: create table blah(a int not null primary key clustered); insert blah values (1), (2); alter table blah add b int null constraint df_blah_b default (0); select * from blah;вы увидите 2 пустых значения для столбца b.
ErikE 14.06.2013 22:19:54
Используйте WITH VALUESдля обновления существующих обнуляемых строк. См. MSDN : «Если добавленный столбец допускает нулевые значения и WITH VALUESуказан, значение по умолчанию сохраняется в новом столбце, добавляется в существующие строки».
Yahoo Serious 28.07.2014 09:45:58
ALTER TABLE ADD ColumnName {Column_Type} Constraint

В статье MSDN ALTER TABLE (Transact-SQL) представлен весь синтаксис таблицы изменений.

39
29.07.2013 21:11:17
ALTER TABLE Protocols
ADD ProtocolTypeID int NOT NULL DEFAULT(1)
GO

Включение DEFAULT заполняет столбец в существующих строках значением по умолчанию, поэтому ограничение NOT NULL не нарушается.

1000
19.04.2016 13:35:13
Проблема с этим ответом состоит в том, что значение по умолчанию действительно только для новых записей. Существующие записи будут по-прежнему иметь значение NULL.
Roee Gavirel 9.11.2011 10:22:08
Вы обнаружите, что это не так. В противном случае ограничение будет нарушено.
dbugger 9.11.2011 16:50:24
Столбцы в существующих строках заполняются значением по умолчанию. Небольшой эмпирический тест докажет это.
dbugger 9.11.2011 16:57:03
Просто чтобы уточнить - если в команде не указано «NOT NULL», значение для существующих строк НЕ будет обновлено и останется NULL. Если в команду включено «NOT NULL», значение для существующих строк будет обновлено в соответствии со значением по умолчанию.
Stack Man 14.08.2012 22:11:38
Для нескольких столбцов ALTER TABLE table_1 ADD col_1 int NOT NULL ПО УМОЛЧАНИЮ (1), col_2 int NULL
aads 4.09.2013 05:25:11
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
136
31.07.2009 03:46:24
Это не будет работать, если в таблице уже есть содержимое, потому что новый столбец «не обнуляемый» создается до ограничения по умолчанию
WDuffy 16.05.2016 14:16:35

Остерегайтесь, когда столбец, который вы добавляете, имеет NOT NULLограничение, но не имеет DEFAULTограничения (значения). Оператор ALTER TABLEпотерпит неудачу в том случае, если в таблице есть какие-либо строки. Решение состоит в том, чтобы либо удалить NOT NULLограничение из нового столбца, либо предоставить DEFAULTдля него ограничение.

100
24.09.2008 16:03:25
любой пример SQL об этом?
Kiquenet 23.08.2016 11:50:28

Использование:

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

ALTER TABLE myTableName  
ADD RecordAddedDate SMALLDATETIME NULL DEFAULT (GETDATE())  
GO 
80
27.02.2020 08:53:11

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

ALTER TABLE table
ADD column BIT     -- Demonstration with NULL-able column added
CONSTRAINT Constraint_name DEFAULT 0 WITH VALUES
233
26.05.2017 19:11:03
Это ключевой момент. Легко предположить, что столбец с DEFAULTограничением всегда будет иметь значение, то есть не будет NULL, даже если NOT NULLон не указан.
Bill Brinkley 27.11.2012 19:29:03
@ tkocmathla О, я не говорил о типе BITданных, я говорил об этой конкретной BIT колонке . Посмотрите на ответ, столбец объявлен как NOT NULL.
rsenna 20.08.2014 19:57:19

Вы можете сделать это с T-SQL следующим образом.

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

Кроме того, вы можете использовать SQL Server Management Studio также, щелкнув правой кнопкой мыши по таблице в меню «Дизайн», установив значение по умолчанию для таблицы.

И более того, если вы хотите добавить один и тот же столбец (если он не существует) ко всем таблицам в базе данных, используйте:

 USE AdventureWorks;
 EXEC sp_msforeachtable
'PRINT ''ALTER TABLE ? ADD Date_Created DATETIME DEFAULT GETDATE();''' ;
52
26.05.2017 19:15:40

В SQL Server 2008-R2 я перехожу в режим разработки - в тестовую базу данных - и добавляю свои два столбца с помощью конструктора и выполняю настройки с помощью графического интерфейса, а затем печально известный Right-Clickдает опцию « Создать сценарий изменения »!

Появится всплывающее окно с небольшим, как вы уже догадались, надлежащим образом отформатированным скриптом с гарантированными изменениями. Нажмите легкую кнопку.

50
26.05.2017 19:17:22

Использование:

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

Ссылка: ALTER TABLE (Transact-SQL) (MSDN)

56
26.05.2017 19:18:28

Самая простая версия с двумя строками

ALTER TABLE MyTable
ADD MyNewColumn INT NOT NULL DEFAULT 0
98
25.05.2012 14:50:21

Кроме того, вы можете добавить значение по умолчанию, не называя ограничение явно:

ALTER TABLE [schema].[tablename] ADD  DEFAULT ((0)) FOR [columnname]

Если у вас есть проблема с существующими ограничениями по умолчанию при создании этого ограничения, то их можно удалить:

alter table [schema].[tablename] drop constraint [constraintname]
48
26.05.2017 19:19:09
К вашему сведению, явное именование ограничения с использованием стандартной схемы именования (например, «DF_Table_Column») для простоты обслуживания. В противном случае поиск ограничения требует больше работы.
Mike Christian 22.11.2017 00:15:12
ALTER TABLE MYTABLE ADD MYNEWCOLUMN VARCHAR(200) DEFAULT 'SNUGGLES'
128
16.11.2012 23:33:24
это добавление null! должно быть не null раньше
baaroz 17.09.2013 19:55:53
@baaroz, это работает с NOT NULL: ALTER TABLE MYTABLE ADD MYNEWCOLUMN VARCHAR (200) NOT NULL ПО УМОЛЧАНИЮ 'SNUGGLES'
shaijut 22.02.2015 09:59:14

Пример:

ALTER TABLE [Employees] ADD Seniority int not null default 0 GO
31
12.07.2014 06:39:13

Чтобы добавить столбец в существующую таблицу базы данных со значением по умолчанию, мы можем использовать:

ALTER TABLE [dbo.table_name]
    ADD [Column_Name] BIT NOT NULL
Default ( 0 )

Вот еще один способ добавить столбец в существующую таблицу базы данных со значением по умолчанию.

Ниже приведен гораздо более подробный сценарий SQL для добавления столбца со значением по умолчанию, включая проверку существования столбца перед его добавлением, а также проверку ограничения и удаление его, если оно есть. Этот сценарий также называет ограничение, чтобы у нас было хорошее соглашение об именах (мне нравится DF_), и если нет, SQL даст нам ограничение с именем, которое имеет случайно сгенерированный номер; так что приятно иметь возможность назвать ограничение тоже.

-------------------------------------------------------------------------
-- Drop COLUMN
-- Name of Column: Column_EmployeeName
-- Name of Table: table_Emplyee
--------------------------------------------------------------------------
IF EXISTS (
            SELECT 1
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_NAME = 'table_Emplyee'
              AND COLUMN_NAME = 'Column_EmployeeName'
           )
    BEGIN

        IF EXISTS ( SELECT 1
                    FROM sys.default_constraints
                    WHERE object_id = OBJECT_ID('[dbo].[DF_table_Emplyee_Column_EmployeeName]')
                      AND parent_object_id = OBJECT_ID('[dbo].[table_Emplyee]')
                  )
            BEGIN
                ------  DROP Contraint

                ALTER TABLE [dbo].[table_Emplyee] DROP CONSTRAINT [DF_table_Emplyee_Column_EmployeeName]
            PRINT '[DF_table_Emplyee_Column_EmployeeName] was dropped'
            END
     --    -----   DROP Column   -----------------------------------------------------------------
        ALTER TABLE [dbo].table_Emplyee
            DROP COLUMN Column_EmployeeName
        PRINT 'Column Column_EmployeeName in images table was dropped'
    END

--------------------------------------------------------------------------
-- ADD  COLUMN Column_EmployeeName IN table_Emplyee table
--------------------------------------------------------------------------
IF NOT EXISTS (
                SELECT 1
                FROM INFORMATION_SCHEMA.COLUMNS
                WHERE TABLE_NAME = 'table_Emplyee'
                  AND COLUMN_NAME = 'Column_EmployeeName'
               )
    BEGIN
    ----- ADD Column & Contraint
        ALTER TABLE dbo.table_Emplyee
            ADD Column_EmployeeName BIT   NOT NULL
            CONSTRAINT [DF_table_Emplyee_Column_EmployeeName]  DEFAULT (0)
        PRINT 'Column [DF_table_Emplyee_Column_EmployeeName] in table_Emplyee table was Added'
        PRINT 'Contraint [DF_table_Emplyee_Column_EmployeeName] was Added'
     END

GO

Это два способа добавить столбец в существующую таблицу базы данных со значением по умолчанию.

54
29.11.2017 19:13:11

Попробуй это

ALTER TABLE Product
ADD ProductID INT NOT NULL DEFAULT(1)
GO
18
1.09.2014 07:17:52

Если вы хотите добавить несколько столбцов, вы можете сделать это следующим образом:

ALTER TABLE YourTable
    ADD Column1 INT NOT NULL DEFAULT 0,
        Column2 INT NOT NULL DEFAULT 1,
        Column3 VARCHAR(50) DEFAULT 'Hello'
GO
78
14.11.2017 20:54:08

Пример:

ALTER TABLE tes 
ADD ssd  NUMBER   DEFAULT '0';
23
15.04.2015 05:13:58

SQL Server + Изменить таблицу + Добавить столбец + Значение по умолчанию uniqueidentifier

ALTER TABLE Product 
ADD ReferenceID uniqueidentifier not null 
default (cast(cast(0 as binary) as uniqueidentifier))
18
7.07.2015 08:38:01

SQL Server + Изменить таблицу + Добавить столбец + Значение по умолчанию uniqueidentifier ...

ALTER TABLE [TABLENAME] ADD MyNewColumn INT not null default 0 GO
6
7.12.2015 10:44:35

Это можно сделать и в графическом интерфейсе SSMS. Я показываю дату по умолчанию ниже, но значение по умолчанию может быть любым, конечно.

  1. Переведите вашу таблицу в режим конструктора (щелкните правой кнопкой мыши по таблице в проводнике объектов-> Дизайн)
  2. Добавьте столбец в таблицу (или щелкните столбец, который вы хотите обновить, если он уже существует)
  3. В свойствах столбца ниже введите (getdate())или abcили 0любое другое значение в поле « Значение по умолчанию» или «Связывание», как показано ниже:

введите описание изображения здесь

43
16.07.2018 15:36:51

Добавьте новый столбец в таблицу:

ALTER TABLE [table]
ADD Column1 Datatype

Например,

ALTER TABLE [test]
ADD ID Int

Если пользователь хочет увеличить его автоматически, тогда:

ALTER TABLE [test]
ADD ID Int IDENTITY(1,1) NOT NULL
14
26.05.2017 19:21:41
IF NOT EXISTS (
    SELECT * FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME ='TABLENAME' AND COLUMN_NAME = 'COLUMNNAME'
)
BEGIN
    ALTER TABLE TABLENAME ADD COLUMNNAME Nvarchar(MAX) Not Null default
END
18
21.06.2016 11:29:50
Мне нравится NOT EXISTSчек, прежде чем пытаться изменить таблицу. Очень хорошее решение Некоторые дополнительные комментарии о том, как это работает, сделают его еще более полезным.
Michael Gaskill 21.06.2016 20:14:15

Если по умолчанию установлено значение Null, то:

  1. В SQL Server откройте дерево целевой таблицы
  2. Щелкните правой кнопкой мыши «Колонны» ==> New Column
  3. Введите имя столбца Select Typeи установите флажок Разрешить пустые
  4. В строке меню нажмите Save

Выполнено!

8
2.01.2017 20:07:02
ALTER TABLE tbl_table ADD int_column int NOT NULL DEFAULT(0)

Из этого запроса вы можете добавить столбец целого типа данных со значением по умолчанию 0.

9
24.05.2016 11:20:56

Это можно сделать с помощью приведенного ниже кода.

CREATE TABLE TestTable
    (FirstCol INT NOT NULL)
    GO
    ------------------------------
    -- Option 1
    ------------------------------
    -- Adding New Column
    ALTER TABLE TestTable
    ADD SecondCol INT
    GO
    -- Updating it with Default
    UPDATE TestTable
    SET SecondCol = 0
    GO
    -- Alter
    ALTER TABLE TestTable
    ALTER COLUMN SecondCol INT NOT NULL
    GO
11
26.05.2017 19:23:03

Сначала создайте таблицу с именем студента:

CREATE TABLE STUDENT (STUDENT_ID INT NOT NULL)

Добавьте к нему один столбец:

ALTER TABLE STUDENT 
ADD STUDENT_NAME INT NOT NULL DEFAULT(0)

SELECT * 
FROM STUDENT

Таблица создается, и в существующую таблицу добавляется столбец со значением по умолчанию.

Изображение 1

22
29.05.2017 08:28:06

Ну, теперь у меня есть некоторые изменения в моем предыдущем ответе. Я заметил, что ни один из ответов не упоминается IF NOT EXISTS. Поэтому я собираюсь предложить новое решение, поскольку столкнулся с некоторыми проблемами при изменении таблицы.

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.columns WHERE table_name = 'TaskSheet' AND column_name = 'IsBilledToClient')
BEGIN
ALTER TABLE dbo.TaskSheet ADD
 IsBilledToClient bit NOT NULL DEFAULT ((1))
END
GO

Вот TaskSheetконкретное имя таблицы и IsBilledToClientновый столбец, который вы собираетесь вставить, и 1значение по умолчанию. Это означает, что в новом столбце будет значение существующих строк, поэтому один из них будет установлен там автоматически. Тем не менее, вы можете изменить, как пожелаете, с учетом типа столбца, который я использовал BIT, поэтому я установил значение по умолчанию 1.

Я предлагаю вышеупомянутую систему, потому что я столкнулся с проблемой. Так в чем проблема? Проблема в том, что если IsBilledToClientстолбец существует в таблице, то, если вы выполните только часть кода, приведенную ниже, вы увидите ошибку в построителе запросов SQL-сервера. Но если он не существует, то в первый раз не будет ошибки при выполнении.

ALTER TABLE {TABLENAME}
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL}
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
[WITH VALUES]
9
26.05.2017 19:26:55

Вы можете использовать этот запрос:

ALTER TABLE tableName ADD ColumnName datatype DEFAULT DefaultValue;
8
26.05.2017 19:27:12

На это есть много ответов, но я чувствую необходимость добавить этот расширенный метод. Это кажется намного дольше, но это чрезвычайно полезно, если вы добавляете поле NOT NULL в таблицу с миллионами строк в активной базе данных.

ALTER TABLE {schemaName}.{tableName}
    ADD {columnName} {datatype} NULL
    CONSTRAINT {constraintName} DEFAULT {DefaultValue}

UPDATE {schemaName}.{tableName}
    SET {columnName} = {DefaultValue}
    WHERE {columName} IS NULL

ALTER TABLE {schemaName}.{tableName}
    ALTER COLUMN {columnName} {datatype} NOT NULL

Для этого нужно добавить столбец как пустое поле со значением по умолчанию, обновить все поля до значения по умолчанию (или вы можете назначить более значимые значения), и, наконец, столбец будет иметь значение NOT NULL.

Причина этого заключается в том, что если вы обновляете крупномасштабную таблицу и добавляете новое ненулевое поле, оно должно записываться в каждую строку и тем самым блокирует всю таблицу по мере добавления столбца, а затем записывает все значения.

Этот метод добавляет столбец NULL, который работает намного быстрее сам по себе, а затем заполняет данные перед установкой ненулевого статуса.

Я обнаружил, что выполнение всего этого за одно утверждение блокирует одну из наших более активных таблиц на 4-8 минут, и довольно часто я убиваю процесс. Этот метод каждой части обычно занимает всего несколько секунд и вызывает минимальную блокировку.

Кроме того, если у вас есть таблица размером в миллиарды строк, возможно, стоит обновить пакет следующим образом:

WHILE 1=1
BEGIN
    UPDATE TOP (1000000) {schemaName}.{tableName}
        SET {columnName} = {DefaultValue}
        WHERE {columName} IS NULL

    IF @@ROWCOUNT < 1000000
        BREAK;
END
18
26.05.2017 19:29:56