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

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

вопрос задан 18.09.2008
Mathias
12304 репутация

37 ответов


  • 2967 рейтинг

    Синтаксис:

     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_SomeCol то SQL Server будет автогенерировать
    По умолчанию-Contraint со смешным названием типа: DF__SomeTa__SomeC__4FB7FEF6

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

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

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

    ответ дан James Boother, с репутацией 30693, 18.09.2008
  • 875 рейтинг
     ALTER TABLE Protocols
    ADD ProtocolTypeID int NOT NULL DEFAULT(1)
    GO
     

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

    ответ дан dbugger, с репутацией 11086, 18.09.2008
  • 199 рейтинг

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

     ALTER TABLE table
    ADD column BIT     -- Demonstration with NULL-able column added
    CONSTRAINT Constraint_name DEFAULT 0 WITH VALUES
     
    ответ дан phunk_munkie, с репутацией 1991, 12.04.2011
  • 116 рейтинг
    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
    
    ответ дан ddc0660, с репутацией 3344, 18.09.2008
  • 106 рейтинг
    ALTER TABLE MYTABLE ADD MYNEWCOLUMN VARCHAR(200) DEFAULT 'SNUGGLES'
    
    ответ дан Evan V, с репутацией 1299, 5.08.2012
  • 86 рейтинг

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

    ответ дан jalbert, с репутацией 2406, 24.09.2008
  • 81 рейтинг

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

     ALTER TABLE MyTable
    ADD MyNewColumn INT NOT NULL DEFAULT 0
     
    ответ дан adeel41, с репутацией 1992, 25.05.2012
  • 66 рейтинг

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

     -- Add a column with a default DateTime  
    -- to capture when each record is added.
    
    ALTER TABLE myTableName  
    ADD RecordAddedDate smalldatetime NULL DEFAULT(GetDate())  
    GO 
     
    ответ дан JerryOL, с репутацией 981, 9.12.2010
  • 58 рейтинг

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

     ALTER TABLE YourTable
        ADD Column1 INT NOT NULL DEFAULT 0,
            Column2 INT NOT NULL DEFAULT 1,
            Column3 VARCHAR(50) DEFAULT 'Hello'
    GO
     
    ответ дан Gabriel L., с репутацией 2315, 14.10.2014
  • 46 рейтинг

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

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

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

    ответ дан giá vàng, с репутацией 461, 27.12.2011
  • 43 рейтинг

    Вы можете сделать это с 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();''' ;
     
    ответ дан gngolakia, с репутацией 1795, 27.11.2011
  • 42 рейтинг

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

    Как вы уже догадались, Bang up вытаскивает небольшое окно с правильно отформатированным скриптом с гарантированным сменой работы. Нажмите на кнопку.

    ответ дан Jack, с репутацией 437, 1.12.2011
  • 41 рейтинг

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

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

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

     alter table [schema].[tablename] drop constraint [constraintname]
     
    ответ дан Christo, с репутацией 1590, 1.06.2012
  • 39 рейтинг

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

     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
     

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

    ответ дан Catto, с репутацией 4315, 20.06.2014
  • 35 рейтинг
     ALTER TABLE ADD ColumnName {Column_Type} Constraint
     

    В статье MSDN ALTER TABLE (Transact-SQL) есть все синтаксис alter table.

    ответ дан Benjamin Autin, с репутацией 3641, 18.09.2008
  • 27 рейтинг

    Пример:

     ALTER TABLE [Employees] ADD Seniority int not null default 0 GO
     
    ответ дан andy, с репутацией 305, 5.03.2014
  • 24 рейтинг

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

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

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

    ответ дан Tony L., с репутацией 8818, 10.12.2015
  • 21 рейтинг

    Пример:

     ALTER TABLE tes 
    ADD ssd  NUMBER   DEFAULT '0';
     
    ответ дан Mohit Tamrakar, с репутацией 330, 25.11.2014
  • 16 рейтинг

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

     ALTER TABLE Product 
    ADD ReferenceID uniqueidentifier not null 
    default (cast(cast(0 as binary) as uniqueidentifier))
     
    ответ дан Naveen Desosha, с репутацией 262, 15.06.2015
  • 16 рейтинг

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

     CREATE TABLE STUDENT (STUDENT_ID INT NOT NULL)
     

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

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

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

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

    ответ дан Laxmi, с репутацией 2621, 20.10.2016
  • 15 рейтинг

    Попробуй это

     ALTER TABLE Product
    ADD ProductID INT NOT NULL DEFAULT(1)
    GO
     
    ответ дан Jakir Hossain, с репутацией 306, 1.09.2014
  • 14 рейтинг
    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
    
    ответ дан Jeevan Gharti, с репутацией 195, 26.04.2016
  • 11 рейтинг

    У этого есть много ответов, но я чувствую необходимость добавить этот расширенный метод. Это выглядит намного дольше, но это очень полезно, если вы добавляете поле 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.

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

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

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

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

     WHILE 1=1
    BEGIN
        UPDATE TOP (1000000) {schemaName}.{tableName}
            SET {columnName} = {DefaultValue}
            WHERE {columName} IS NULL
    
        IF @@ROWCOUNT < 1000000
            BREAK;
    END
     
    ответ дан Ste Bov, с репутацией 619, 5.05.2017
  • 11 рейтинг
    --Adding Value with Default Value
    ALTER TABLE TestTable
    ADD ThirdCol INT NOT NULL DEFAULT(0)
    GO
    
    ответ дан wild coder, с репутацией 307, 8.02.2018
  • 10 рейтинг

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

     ALTER TABLE [table]
    ADD Column1 Datatype
     

    Например,

     ALTER TABLE [test]
    ADD ID Int
     

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

     ALTER TABLE [test]
    ADD ID Int IDENTITY(1,1) NOT NULL
     
    ответ дан Chirag Thakar, с репутацией 2004, 22.12.2015
  • 9 рейтинг

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

     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
     
    ответ дан Mohit Dagar, с репутацией 405, 9.09.2016
  • 8 рейтинг

    Ну, теперь у меня есть некоторые изменения в моем предыдущем ответе. Я заметил, что ни один из ответов не упоминался 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]
     
    ответ дан gdmanandamohon, с репутацией 1189, 9.03.2017
  • 8 рейтинг
     ALTER TABLE tbl_table ADD int_column int NOT NULL DEFAULT(0)
     

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

    ответ дан Sandeep Kumar, с репутацией 839, 24.05.2016
  • 7 рейтинг

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

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

    Готово!

    ответ дан usefulBee, с репутацией 4660, 9.05.2016
  • 6 рейтинг

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

     ALTER TABLE [TABLENAME] ADD MyNewColumn INT not null default 0 GO
     
    ответ дан Chanukya, с репутацией 4330, 7.12.2015
  • 5 рейтинг

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

     ALTER TABLE tableName ADD ColumnName datatype DEFAULT DefaultValue;
     
    ответ дан Arun D, с репутацией 231, 15.03.2017
  • 4 рейтинг

    Щелкните правой кнопкой мыши по таблице, щелкните под последним именем столбца и введите данные столбца.

    Затем добавьте значение по умолчанию или привязку, что-то вроде «1» для строки или 1 для int.

    ответ дан Mohamad Shahrestani, с репутацией 260, 9.05.2017
  • 3 рейтинг

    шаг 1. ПЕРВЫЙ ВЫ ДОЛЖНЫ ИЗМЕНЯТЬ ТАБЛИЦУ С ДОБАВЛЕНИЕМ ПОЛЯ

     alter table table_name add field field_name data_type
     

    Шаг 2 СОЗДАЙТЕ ПО УМОЛЧАНИЮ

     USE data_base_name;
    GO
    CREATE DEFAULT default_name AS 'default_value';
     

    шаг 3. ТО, ЧТО ВЫ ИСКЛЮЧАЕТЕ ЭТУ ПРОЦЕДУРУ

     exec sp_bindefault 'default_name' , 'schema_name.table_name.field_name'
     

    пример -

     USE master;
    GO
    EXEC sp_bindefault 'today', 'HumanResources.Employee.HireDate';
     
    ответ дан raju chowrsiya, с репутацией 49, 4.10.2017
  • 2 рейтинг
    ALTER TABLE Table1 ADD Col3 INT NOT NULL DEFAULT(0)
    
    ответ дан Krishan Dutt Sharma, с репутацией 86, 13.02.2018
  • 1 рейтинг
     --Adding New Column with Default Value
    ALTER TABLE TABLENAME 
    ADD COLUMNNAME DATATYPE NULL|NOT NULL DEFAULT (DEFAULT_VALUE)
     

    ИЛИ ЖЕ

     --Adding CONSTRAINT And Set Default Value on Column
    ALTER TABLE TABLENAME ADD  CONSTRAINT [CONSTRAINT_Name]  DEFAULT 
    (DEFAULT_VALUE) FOR [COLUMNNAME]
     
    ответ дан Erfan Mohammadi, с репутацией 102, 15.04.2018
  • 0 рейтинг

    Используйте базовую версию запроса

    ALTER TABLE MyTable ADD MyNewColumn DataType DEFAULT DefaultValue

    Это добавит новый столбец в запрос.

    ответ дан Anshul Dubey, с репутацией 54, 28.08.2018
  • 0 рейтинг
    This is for Sql Server
    
    ALTER TABLE TableName
            ADD ColumnName (type) ->NULL OR NOT NULL
        DEFAULT (default value)
           WITH VALUES
    Example
    
    ALTER TABLE Activities
            ADD status int NOT NULL DEFAULT (0)
           WITH VALUES
    if want to add constraints then ->
    
    ALTER TABLE Table_1
            ADD row3 int NOT NULL
     CONSTRAINT CONSTRAINT_NAME DEFAULT (0)
           WITH VALUES
    
    ответ дан Akhil Singh, с репутацией 162, 5.02.2018