Sql добавить колонку со значениями

SQL-Ex blog

Новости сайта «Упражнения SQL», статьи и переводы

Вставка столбца со значением по умолчанию в таблицу SQL Server

Чтобы вставить данные в таблицу, имеющую столбцы со ограничениями по умолчанию, мы можем использовать ограничение DEFAULT для вставки принятого значения по умолчанию в таблицу. Здесь рассматриваются следующие вопросы:

• Ограничение DEFAULT и необходимые разрешения для его создания.
• Добавление ограничения DEFAULT при создании новой таблицы.
• Добавление ограничения DEFAULT в существующую таблицу.
• Модификация и просмотр определения ограничения с помощью скриптов T-SQL и в SSMS.

Что такое ограничение DEFAULT

Ограничение DEFAULT задает значение по умолчанию для столбца.

Когда выполняется оператор INSERT, но не указывается конкретное значение для столбца с созданным ограничением DEFAULT, SQL Server вставляет значение по умолчанию, указанное в определении ограничения DEFAULT.

Чтобы создать ограничение по умолчанию, вам необходимо иметь разрешение на выполнение ALTER TABLE и CREATE TABLE.

Добавление ограничения DEFAULT при создании новой таблицы

Это будет таблица с именем SalesDetails. Когда мы вставляем данные в эту таблицу без указания значения для столбца Sale_Qty, запрос должен вставить нуль. Чтобы добиться этого, я создаю ограничение по умолчанию с именем DF_SalesDetails_SaleQty на столбце Sale_Qty.

Давайте теперь протестируем поведение ограничения, вставив несколько фиктивных записей в таблицу. Выполните следующий запрос:

Теперь посмотрим, что находится в таблице:

Как можно увидеть, в столбец Sale_Qty был вставлен нуль.

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

Создайте таблицу с помощью следующего запроса:

Выполните следующий скрипт, чтобы увидеть имя ограничения:

SQL Server создал ограничение со сгенерированным системой именем.

Добавление ограничение DEFAULT в существующую таблицу

Чтобы добавить ограничение для существующего столбца таблицы, используется оператор ALTER TABLE ADD CONSTRAINT:

Здесь

• tbl_name : задает имя таблицы, в которую вы хотите добавить ограничение по умолчанию.
• constraint_name : задает желаемое имя ограничения.
• column_name : задает имя столбца, для которого вы хотите создать ограничение по умолчанию.
• default_value : задает значение, которое вы хотите использовать при вставке.

Когда значение column_name не задается в операторе INSERT, вставляется значение, указанное в параметре default_value.

Давайте сначала добавим столбец Product_name в SalesDetails:

Вставляем данные в таблицу без указания значения для столбца Product_name. Запрос должен вставить N/A.

Для этого я создам ограничение по умолчанию с именем DF_SalesDetails_ProductName на столбце Product_name. Следующий запрос создает это ограничение:

Теперь давайте проверим действие ограничения. Вставим запись, не указывая имя товара:

После вставки записей выполним оператор SELECT, чтобы просмотреть данные:

Как видно на рисунке, значением столбца Product_name для PROD0003 является N/A.

Изменение ограничения DEFAULT

Мы можем изменить определение ограничения по умолчанию: сначала удалить существующее ограничение, а затем создать ограничение с другим определением.
Предположим, что вместо вставки N/A мы хотим вставлять Not Applicable. Сначала мы должны удалить ограничение DF_SalesDetails_ProductName. Выполните следующий запрос:

После удаления ограничения выполните запрос для создания ограничения:

Теперь давайте вставим запись без указания имени товара:

Выполните оператор SELECT для просмотра данных в таблице SalesDetails:

Видно, что значением столбца Product_name является Not Applicable.

Просмотр ограничения DEFAULT

Мы можем увидеть список ограничений DEFAULT с помощью Server Management Studio и выполнив запрос к динамическим административным представлениям.

Откройте SSMS и разверните Databases > DemoDatabase > SalesDetails > Constraint:

Видно, что созданы два ограничения с именами DF_SalesDetails_SaleQty и DF_SalesDetails_ProductName.

Другой способ просмотра ограничений — запрос к sys.default_constraints. Следующий запрос выводит список ограничений по умолчанию и их определения:

Мы можем использовать хранимую процедуру sp_helpconstraint для просмотра списка ограничений, созданных в таблице:

В столбце constraint_keys выводится определение ограничения по умолчанию.

Удаление ограничения

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

• Оператор ALTER TABLE DROP CONSTRAINT.
• Оператор DROP DEFAULT.

Чтобы удалить ограничение, можно использовать команду ALTER TABLE DROP CONSTRAINT. Она имеет следующий синтаксис:

Здесь

• tbl_name: задает имя таблицы, которая содержит столбец со значением по умолчанию.
• constraint_name: задает имя ограничения, которое требуется удалить.

Предположим, мы хотим удалить ограничение DF_SalesDetails_SaleQty из таблицы SalesDetails. Выполните запрос:

Проверим, что ограничение было удалено:

Рассмотрим теперь оператор DROP DEFAULT. Он имеет следующий синтаксис:

где constraint_name задает имя ограничения, которое требуется удалить.

Чтобы удалить ограничение с помощью оператора DROP DEFAULT, выполните следующий запрос:

Надеюсь, что эта информация и практические примеры поможет в вашей работе.

Обратные ссылки

Нет обратных ссылок

Комментарии

Показывать комментарии Как список | Древовидной структурой

Автор не разрешил комментировать эту запись

Источник

Как добавить новый столбец в таблицу между существующими столбцами?

Уже не впервые на форумах встречаю вопрос о том, как добавить новый столбец в определенное место существующей таблицы, скажем, между первым и вторым столбцом. Этот наивный с точки зрения реляционной модели вопрос, тем не менее, имеет некоторый смысл с точки зрения языка SQL.

Я говорю «наивный», поскольку по определению атрибуты отношения не упорядочены, и обращение к значениям атрибута выполняется по его имени, но не по позиции. Что же касается языка SQL, то столбцы в таблице имеют порядок, который задается в операторе CREATE TABLE. Новый же столбец, который добавляется с помощью оператора ALTER TABLE, становится последним в таблице. Т.е. стандарт языка SQL не предусматривает возможности непосредственно добавить столбец в определенную позицию в списке столбцов.

Справедливости ради следует сказать, что некоторые реализации языка SQL расширяют стандарт в этом плане. Например, в MySQL в операторе ALTER TABLE вы можете указать позицию добавляемого столбца (новый столбец может стать первым или после указанного столбца).

Другой вопрос, а зачем это нужно? Мне приходит в голову такой вариант. Скажем, в клиентском приложении для генерации отчетов используется запрос типа

Если потребовалось добавить в таблицу Employees дополнительную информацию о сотрудниках, которая логически должна находиться в определенном месте (например, отчество непосредственно между именем и фамилией), то вместо того, чтобы вносить изменения в клиентские приложения, может оказаться проще изменить структуру таблицы Employees.

Итак, имеется таблица Employees, которая создается следующим оператором:

Теперь нам требуется добавить столбец middle_name (отчество) между столбцами first_name и last_name.

В MySQL это можно сделать просто:

В SQL Server так поступить нельзя, но можно использовать следующий алгоритм:

» создание новой таблицы требуемой структуры;
» копирование данных из таблицы Employees в эту новую таблицу;
» удаление таблицы Employees;
» переименование новой таблицы в таблицу с именем Employees.

Ниже приводятся операторы T-SQL, которые реализуют этот алгоритм.

Обратите внимание, что столбец middle_name допускает NULL-значения. Мы не можем добавить столбец в существующую таблицу (или, как в нашем случае, не задавая значения для этого столбца при копировании данных из таблицы Employees в таблицу Emp_temp), если он не имеет значения по умолчанию. Здесь мы принимаем по умолчанию значение NULL.

Мы можем выполнить два первых шага за одно действие с помощью оператора SELECT INTO, который «на лету» создает новую таблицу:

Оператор CAST позволяет нам тут же задать требуемый тип добавляемого столбца. Остальные столбцы наследуют типы из таблицы-источника.

Если вы хотите проверить работу последнего скрипта, приведите таблицу в исходное состояние, удалив добавленный ранее столбец:

Заметим, что при использовании оператора SELECT INTO теряются ключи. Поэтому нам придется добавить ограничение PRIMARY KEY (первичный ключ) либо во временную таблицу, либо уже в переименованную, чтобы получить в точности требуемую структуру:

Аналогичный алгоритм можно применить и для перестановки уже существующих столбцов. Помимо указанной причины такая перестановка может повысить производительность, связанную с сокращением объема данных, записываемых в журнал транзакций в некоторых реализациях. Это связано со спецификой обработки строк фиксированной и переменной длины. Вот какие рекомендации по этому поводу дает Джо Селко * :

» помещайте первыми нечасто обновляемые столбцы постоянной длины;
» затем помещайте нечасто обновляемые столбцы переменной длины;
» последними помещайте часто обновляемые столбцы;
» ставьте рядом столбцы, которые, как правило, обновляются одновременно.

* Селко Д. Стиль программирования Джо Селко на SQL. — М.: Изд-во «Русская редакция»; СПб.: Питер, 2006

Источник

Добавление столбцов в таблицу (компонент Database Engine)

Применимо к: SQL Server 2016 (13.x); и более поздние версии База данных SQL Azure Управляемый экземпляр SQL Azure Azure Synapse Analytics Параллельное хранилище данных

В этой статье содержатся инструкции по добавлению новых столбцов в таблицу в SQL Server при помощи SQL Server Management Studio или Transact-SQL.

Перед началом

Ограничения

Использование инструкции ALTER TABLE для добавления столбцов в таблицу приводит к автоматическому добавлению этих столбцов в конец таблицы. Если требуется, чтобы столбцы располагались в таблице в определенном порядке, воспользуйтесь SQL Server Management Studio. Однако помните, что это не рекомендуемый метод конструирования баз данных. Рекомендуется указывать порядок, в котором возвращаются столбцы, на уровне приложения и запроса. Не следует предполагать, что SELECT * будет возвращать все столбцы в ожидаемом порядке, основанном на порядке их определения в таблице. Всегда указывайте столбцы в запросах и приложениях по именам в том порядке, в котором они должны следовать.

безопасность

Permissions

Требуется разрешение ALTER на таблицу.

Использование среды SQL Server Management Studio

Вставка в таблицу столбцов с помощью конструктора таблиц

В обозревателе объектов щелкните правой кнопкой мыши таблицу, в которую необходимо добавить столбцы, и выберите пункт Конструктор.

Щелкните первую пустую ячейку в столбце Имя столбца .

Введите имя столбца в ячейку. Имя столбца — значение, которое необходимо указать.

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

Это — обязательное значение, и если его не указать, будет использоваться значение по умолчанию.

Значения по умолчанию можно установить или изменить в диалоговом окне Параметры в Инструменты для баз данных.

Продолжайте определение других свойств столбца во вкладке Свойства столбца .

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

По окончании добавления столбцов из меню Файл выберите пункт Сохранить имя таблицы.

Источник

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

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

ОТВЕТЫ

Ответ 1

Синтаксис:

Пример:

Заметки:

Необязательное имя ограничения:
Если вы не CONSTRAINT D_SomeTable_SomeCol SQL Server автоматически сгенерирует
ограничение по умолчанию с забавным именем, например: DF__SomeTa__SomeC__4FB7FEF6

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

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

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

Ответ 2

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

Ответ 3

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

Ответ 4

Ответ 5

Ответ 6

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

Ответ 7

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

Ответ 8

Ответ 9

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

Ответ 10

Ответ 11

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

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

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

Ответ 12

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

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

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

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

Ответ 13

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

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

Ответ 14

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

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

Ответ 15

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

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

Ответ 16

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

Ответ 17

Ответ 18

Ответ 19

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

Добавьте в него один столбец:

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

Ответ 20

Ответ 21

SQL Server + Изменить таблицу + Добавить столбец + Значение по умолчанию Уникальный идентификатор

Ответ 22

Ответ 23

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

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

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

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

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

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

Ответ 24

Ответ 25

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

Например,

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

Ответ 26

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

Ответ 27

Это для SQL Server:

Если вы хотите добавить ограничения, то:

Ответ 28

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

Ответ 29

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

• В SQL Server откройте дерево целевой таблицы
• Щелкните правой кнопкой мыши «Столбцы» == > New Column
• Введите столбец Name, Select Type и отметьте флажок Разрешить Nulls
• В строке меню нажмите Save

Ответ 30

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

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

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

Источник