Как вставить столбец в sql

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

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

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

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

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

 SELECT * FROM Employees ORDER BY last_name, first_name;

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

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

 CREATE TABLE Employees( emp_num INT NOT NULL PRIMARY KEY, first_name CHAR(30) NOT NULL, last_name CHAR(30) NOT NULL );

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

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

 ALTER TABLE Employees ADD COLUMN middle_name CHAR(10) NULL AFTER first_name;

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

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

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

 -- Создаем временную таблицу требуемой структуры CREATE TABLE Emp_temp( emp_num INT NOT NULL PRIMARY KEY, first_name CHAR(30) NOT NULL, middle_name CHAR(30) NULL, last_name CHAR(30) NOT NULL ); GO -- Копируем данные из старой таблицы в новую INSERT INTO Emp_temp(emp_num, first_name, last_name) SELECT * FROM Employees; GO -- Удаляем старую таблицу DROP TABLE Employees; GO -- Переименовываем новую EXEC sp_rename 'Emp_temp', 'Employees'; GO

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

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

 SELECT emp_num, first_name, CAST(NULL AS CHAR(30)), last_name INTO Emp_temp FROM Employees;

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

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

 ALTER TABLE Employees DROP COLUMN middle_name;

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

 ALTER TABLE Emp_temp ADD CONSTRAINT emp_PK PRIMARY KEY(emp_num);

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

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

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

SQL-Ex blog

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

Добавил Sergey Moiseenko on Суббота, 2 октября. 2021

Ограничение 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.

USE demodatabase 
go 
CREATE TABLE salesdetails 
( 
id INT IDENTITY (1, 1), 
product_code VARCHAR(10), 
sale_qty INT CONSTRAINT df_salesdetails_saleqty DEFAULT 0 
)

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

INSERT INTO salesdetails (product_code) 
VALUES ('PROD0001')

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

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

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

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

USE demodatabase 
go 
CREATE TABLE salesdetails 
( 
id INT IDENTITY (1, 1), 
product_code VARCHAR(10), 
sale_qty INT DEFAULT 0 
)

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

SELECT NAME [Constraint name], 
parent_object_id [Table Name], 
type_desc [Object Type], 
definition [Constraint Definition] 
FROM sys.default_constraints

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

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

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

ALTER TABLE [tbl_name] 
ADD CONSTRAINT [constraint_name] DEFAULT [default_value] FOR [Column_name]

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

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

ALTER TABLE salesdetails 
ADD product_name VARCHAR(500)

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

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

ALTER TABLE dbo.salesdetails 
ADD CONSTRAINT df_salesdetails_productname DEFAULT 'N/A' FOR product_name

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

INSERT INTO salesdetails 
(product_code, 
product_name, 
sale_qty) 
VALUES ('PROD0002', 
'Dell Optiplex 7080', 
20) 
INSERT INTO salesdetails 
(product_code, 
sale_qty) 
VALUES ('PROD0003', 
50)

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

USE demodatabase 
go 
SELECT * 
FROM salesdetails 
go

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

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

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

ALTER TABLE dbo.salesdetails 
DROP CONSTRAINT df_salesdetails_productname

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

ALTER TABLE dbo.salesdetails 
ADD CONSTRAINT df_salesdetails_productname DEFAULT 'Not Applicable' FOR 
product_name

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

INSERT INTO salesdetails 
(product_code, 
sale_qty) 
VALUES ('PROD0004', 
10)

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

USE demodatabase 
go 
SELECT * 
FROM salesdetails 
go

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

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

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

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

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

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

SELECT NAME [Constraint name], 
Object_name(parent_object_id)[Table Name], 
type_desc [Consrtaint Type], 
definition [Constraint Definition] 
FROM sys.default_constraints

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

EXEC Sp_helpconstraint 'SalesDetails'

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

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

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

Alter table [tbl_name] drop constraint [constraint_name]

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

ALTER TABLE dbo.salesdetails 
DROP CONSTRAINT [DF_SalesDetails_SaleQty]

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

SELECT NAME [Constraint name], 
Object_name(parent_object_id)[Table Name], 
type_desc [Consrtaint Type], 
definition [Constraint Definition] 
FROM sys.default_constraints

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

DROP DEFAULT [constraint_name]

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

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

IF EXISTS (SELECT NAME 
FROM sys.objects 
WHERE NAME = 'DF_SalesDetails_ProductName' 
AND type = 'D') 
DROP DEFAULT [DF_SalesDetails_ProductName];

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

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

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

ALTER TABLE в SQL

Мы можем изменить структуру таблицы с помощью команды ALTER TABLE. Мы можем:

Добавить столбец в таблицу

Мы можем добавить столбцы в таблицу с помощью команды ALTER TABLE с оператором ADD. Например:

ALTER TABLE Customers
ADD phone varchar ( 10 ) ;

Здесь мы добавили столбец с именем phone в таблицу Customers.

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

Мы также можем добавить сразу несколько столбцов в таблицу. Например:

ALTER TABLE Customers
ADD phone varchar ( 10 ) , age int ;

Здесь мы добавили столбцы phone и age в таблицу Customers.

Переименовать столбец в таблице

Мы можем переименовать столбцы в таблице с помощью команды ALTER TABLE с оператором RENAME COLUMN. Например:

ALTER TABLE Customers
RENAME COLUMN customer_id TO c_id ;

Здесь мы изменили имя столбца customer_id на c_id в таблице Customers.

Изменить столбец в таблице

Мы также можем изменить тип данных столбца с помощью команды ALTER TABLE с оператором MODIFY или ALTER COLUMN. Например:

SQL Server

Как связать данные нескольких столбцов и вставить их в новый столбец одной и той же таблицы

У меня есть таблица где есть данные, но теперь нужно добавить новый столбец (я отдельно добавил ее — new_column) и в нее вставить запись собранной из других столбцов той же таблицы, пытался сделать так но не работает: insert into table t (new_column) select (column_1 || ‘-‘ || column_2 || ‘-‘ || column_3 || ‘-‘ || column_4) nc from table; commit;

Отслеживать

задан 19 янв в 6:35

67 3 3 бронзовых знака

2 ответа 2

Сортировка: Сброс на вариант по умолчанию

В таком случае, лучше всего, подходит вычисляемый столбец:

ALTER TABLE table ADD (nc varchar(512) GENERATED ALWAYS AS (column_1 || '-' || column_2 || '-' || column_3 || '-' || column_4) VIRTUAL);