Инструкция INSERT INTO в Transact-SQL – несколько способов добавления данных в таблицу. Осваиваем инструкцию INSERT INTO SQL

Инструкция INSERT в T-SQL

INSERT – это инструкция языка T-SQL, которая предназначена для добавления данных в таблицу, т.е. создания новых записей. Данную инструкцию можно использовать как для добавления одной строки в таблицу, так и для массовой вставки данных. Для выполнения инструкции INSERT требуется разрешение на вставку данных (INSERT) в целевую таблицу.

Существует несколько способов использования инструкции INSERT в части данных, которые необходимо вставить:

• Перечисление конкретных значений для вставки;
• Указание набора данных в виде запроса SELECT;
• Указание набора данных в виде вызова процедуры, которая возвращает табличные данные.

Заметка! Начинающим рекомендую посмотреть мой видеокурс по T-SQL.

Упрощённый синтаксис

INSERT [INTO] [таблица] (список столбцов, …) VALUES (список значений, …) Или SELECT запрос на выборку Или EXECUTE процедура

Где,

• INSERT INTO – это команда добавления данных в таблицу;
• Таблица – это имя целевой таблицы, в которую необходимо вставить новые записи;
• Список столбцов – это перечень имен столбцов таблицы, в которую будут вставлены данные, разделенные запятыми;
• VALUES – это конструктор табличных значений, с помощью которого мы указываем значения, которые будем вставлять в таблицу;
• Список значений – это значения, которые будут вставлены, разделенные запятыми. Они перечисляются в том порядке, в котором указаны столбцы в списке столбцов;
• SELECT – это запрос на выборку данных для вставки в таблицу. Результирующий набор данных, который вернет запрос, должен соответствовать списку столбцов;
• EXECUTE – это вызов процедуры на получение данных для вставки в таблицу. Результирующий набор данных, который вернет хранимая процедура, должен соответствовать списку столбцов.

Вот примерно так и выглядит упрощённый синтаксис инструкции INSERT INTO, в большинстве случаев именно так Вы и будете добавлять новые записи в таблицы.

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

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

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

Хватит теории, переходим к практике.

Обработка ошибок

Для инструкции INSERT можно реализовать обработку ошибок, указав инструкцию в конструкции TRY…CATCH.

Если инструкция INSERT нарушает ограничение или правило, либо в ней присутствует значение, несовместимое с типом данных столбца, то при выполнении инструкции происходит сбой и отображается сообщение об ошибке.

Если инструкция INSERT загружает несколько строк с помощью инструкции SELECT или EXECUTE, то любые нарушения правил или ограничений, возникающие из-за загружаемых значений, приводят к остановке выполнения инструкции, и ни одна из строк не будет загружена.

Если при выполнении инструкции INSERT возникает арифметическая ошибка (переполнение, деление на ноль или ошибка домена), компонент Компонент Database Engine обрабатывает эти ошибки так же, как если бы параметру SET ARITHABORT было присвоено значение ON. Выполнение пакета прекращается и выводится сообщение об ошибке. Во время оценки выражения, когда параметры SET ARITHABORT и SET ANSI_WARNINGS установлены в значение OFF, если в инструкции INSERT, DELETE или UPDATE происходит арифметическая ошибка переполнения, деления на ноль или ошибка области определения, SQL Server вставляет или обновляет значение NULL. Если целевой столбец не пустой, вставка или обновление не осуществляются, и пользователь получает ошибку.

Совместимость

Если триггер INSTEAD OF определен в операциях INSERT для таблицы или представления, то триггер выполняется вместо инструкции INSERT. Дополнительные сведения о триггерах INSTEAD OF см. в разделе CREATE TRIGGER (Transact-SQL).

Ограничения

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

При использовании выражения TOP в инструкции INSERT строки, на которые имеются ссылки, не упорядочиваются, а предложение ORDER BY не может быть прямо указано в этих инструкциях. Если для вставки строк в значимом хронологическом порядке необходимо использовать предложение TOP, вместе с ним в инструкции подзапроса выборки следует использовать предложение ORDER BY. См. подраздел «Примеры» далее в этом разделе.

Запросы INSERT, которые используют SELECT с ORDER BY для заполнения строк, гарантируют способ вычисления значений идентификатора, но не порядок вставки строк.

В Parallel Data Warehouse предложение ORDER BY недопустимо в инструкциях VIEWS, CREATE TABLE AS SELECT, INSERT SELECT, встраиваемых функциях, производных таблицах, подзапросах и обобщенных табличных выражениях, если также не указать TOP.

Режим ведения журнала

Инструкция INSERT всегда полностью регистрируется в журнале, кроме случаев использования функции OPENROWSET с ключевым словом BULK или выполнения инструкции INSERT INTO SELECT FROM. Для этих операций возможно минимальное протоколирование. Дополнительные сведения см. в подразделе «Рекомендации по массовой загрузке данных» этого раздела.

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

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

При использовании функции OPENROWSET(BULK…) важно понимать, каким образом SQL Server обрабатывает олицетворение. Дополнительные сведения см. в главе «Вопросы безопасности» в разделе Массовый импорт данных при помощи инструкции BULK INSERT или OPENROWSET(BULK…) (SQL Server).

Разрешения

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

Разрешения INSERT предоставлены по умолчанию членам предопределенной роли сервера sysadmin, членам предопределенных ролей баз данных db_owner и db_datawriter, а также владельцу таблицы. Члены ролей sysadmin, db_owner и db_securityadmin, а также владелец таблицы могут передавать разрешения другим пользователям.

Чтобы выполнить инструкцию INSERT с параметром BULK функции OPENROWSET, необходимо быть членом предопределенной роли сервера sysadmin или bulkadmin.

Разница между вставкой и обновлением

Этот раздел также можно назвать, или альтернативные заголовки раздела:

• Разница между INSERT и UPDATE

SQL стейтмент INSERT INTO только вставляет новые записи (строки, ряды) в существующую таблицу, а не вставляет новые значения в ячейки существующих записей (строк, рядов). Вставкой новых значений в ячейки существующих записей (строк, рядов) в SQL занимается оператор обновления UPDATE .

Виды вставки

SQL стейтмент INSERT INTO поддерживает вставку данных в двух видах: вставка данных по значениям и вставка по выборке.

Вставка по значениям

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

Вставка по запросу

Вставка по запросу предусматривает след. синтаксис: INSERT … SELECT. Т.е. сначала данные вытаскиваются из одной таблицы, а затем они вставляются в др. таблицу. При этом должны быть соблюдены след. условия:

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

Исходные данные

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

Примечание!Все примеры будут выполнены в Microsoft SQL Server 2016 Express.

CREATE TABLE TestTable( [Id] [INT] IDENTITY(1,1) NOT NULL, [ProductName] [VARCHAR](100) NOT NULL, [Price] [Money] NOT NULL )

Наша тестовая таблица, будет содержать перечень товаров с ценой.

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

CREATE PROCEDURE TestProcedure AS BEGIN SELECT ProductName, Price FROM TestTable END

Для примера она у нас будет возвращать данные из только что созданной таблицы TestTable.

Пример 1 – Добавляем новую запись в таблицу с использованием конструктора табличных значений

Сначала давайте попробуем добавить одну запись и сразу посмотрим на результат, т.е. напишем запрос на выборку.

INSERT INTO TestTable(ProductName, Price) VALUES (‘Компьютер’, 100) GO SELECT * FROM TestTable

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

После инструкции INSERT я написал инструкцию SELECT и разделил их командой GO.

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

INSERT INTO TestTable(ProductName, Price) VALUES (‘Компьютер’, 100), (‘Клавиатура’, 20), (‘Монитор’, 50) GO SELECT * FROM TestTable

В данном случае мы добавили три записи, т.е. три строки. После VALUES значения каждой новой строки указаны в скобочках, разделили мы их запятыми.

Пример 2 – Добавляем новые строки в таблицу с использованием запроса SELECT

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

INSERT INTO TestTable(ProductName, Price) SELECT ProductName, Price FROM TestTable WHERE Id > 2 GO SELECT * FROM TestTable

В данном примере мы написали запрос SELECT, который возвращает данные из таблицы TestTable, но не все, а только те, у которых идентификатор больше 2. А результат вставили все в ту же таблицу TestTable.

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

INSERT INTO TestTable SELECT ProductName, Price FROM TestTable WHERE Id > 2 GO SELECT * FROM TestTable

В данном случае мы уверены в том, что в таблице TestTable первый столбец это ProductName, а второй Price, поэтому мы можем позволить себе написать именно так. Но, снова повторюсь, на практике лучше указывать список столбцов.

Если Вы заметили, я во всех примерах не указывал столбец Id, а он у нас есть, ошибки не возникло, так как данный столбец со свойством IDENTITY, он автоматически генерирует идентификаторы, поэтому в такой столбец вставить данные просто не получится.

Пример 3 – Добавляем новые записи в таблицу с использованием хранимой процедуры

Сейчас давайте вставим в таблицу данные, которые нам вернёт хранимая процедура. Смысл здесь такой же, вместо VALUES и вместо запроса мы указываем вызов процедуры. Но как Вы понимаете, порядок и количество столбцов, возвращаемых процедурой, должен строго совпадать со списком столбцов для вставки (даже если список столбцов не указан).

INSERT INTO TestTable(ProductName, Price) EXEC TestProcedure GO SELECT * FROM TestTable

Условия выборки

• условие
  WHERE
• логическое ИЛИ
  OR
• логическое И
  AND
• сортировка
  ORDER BY
• ограничение выборки
  LIMIT
• выбор из промежутка
  BETWEEN
• заданные значения
  IN
• T поиск по шаблону
  LIKE
• смена имен полей
  AS
• уникальные значения
  DISTINCT
• отрицание
  NOT
• значение не равно NULL
  IS NOT NULL
• значение равно NULL
  IS NULL

Группировка

• группировка
  GROUP BY
• условие на group by
  HAVING

Сложение строк

• сложение строк
  CONCAT
• сложение с разделителем
  CONCAT_WS
• сложение ячеек
  GROUP_CONCAT

Несколько таблиц

• объединение таблиц
  UNION
• связывание таблиц
  JOIN
• левый join
  LEFT JOIN
• правый join
  RIGHT JOIN
• внутренний join
  INNER JOIN
• копирование в другую таблицу
  SELECT INTO

Длина строк

• длина строки
  LENGTH

Изменение строк

• вырезание слева
  LEFT
• вырезание справа
  RIGHT
• взятие подстроки
  SUBSTRING
• взятие подстроки
  MID
• взятие подстроки
  SUBSTRING_INDEX
• поиск и замена
  REPLACE
• дополнение строки слева
  LPAD
• дополнение строки справа
  RPAD
• переворот строки
  REVERSE
• повторение строки
  REPEAT

1. Добавление целых строк

Как видно из названия, оператор INSERT используется для вставки (добавления) строк в таблицу базы данных. Добавление можно осуществить несколькими способами:

• — добавить одну полную строку
• — добавить часть строки
• — добавить результаты запроса.

Итак, чтобы добавить новую строку в таблицу, нам необходимо указать название таблицы, перечислить названия колонок и указать значение для каждой колонки с помощью конструкции INSERT INTO название_таблицы (поле1, поле2 … ) VALUES (значение1, значение2 …). Рассмотрим на примере.

INSERT INTO Sellers (ID, Address, City, Seller_name, Country) VALUES (‘6’, ‘1st Street’, ‘Los Angeles’, ‘Harry Monroe’, ‘USA’)

Также можно изменять порядок указания названий колонок, однако одновременно нужно менять и порядок значений в параметре VALUES.

2. Добавление части строк

В предыдущем примере при использовании оператора INSERT мы явно отмечали имена столбцов таблицы. Используя данный синтаксис, мы можем пропустить некоторые столбцы. Это значит, что вы вводите значение для одних столбцов но не предлагаете их для других. Например:

INSERT INTO Sellers (ID, City, Seller_name) VALUES (‘6’, ‘Los Angeles’, ‘Harry Monroe’)

В данном примере мы не указали значение для двух столбцов Address и Country . Вы можете исключать некоторые столбцы из оператора INSERT INTO, если это позволяет производить определение таблицы. В этом случае должно соблюдаться одно из условий: этот столбец определен как допускающий значение NULL (отсутствие какого-либо значения) или в определение таблицы указанное значение по умолчанию. Это означает, что, если не указано никакое значение, будет использовано значение по умолчанию. Если вы пропускаете столбец таблицы, которая не допускает появления в своих строках значений NULL и не имеет значения, определенного для использования по умолчанию, СУБД выдаст сообщение об ошибке, и это строка не будет добавлена.

3. Добавление отобранных данных

В предыдущей примерах мы вставляли данные в таблицы, прописывая их вручную в запросе. Однако оператор INSERT INTO позволяет автоматизировать этот процесс, если мы хотим вставлять данные из другой таблицы. Для этого в SQL существует такая кострукция как INSERT INTO … SELECT … . Данная конструкция позволяет одновременно выбирать данные из одной таблицы, и вставить их в другую. Предположим мы имеем еще одну таблицу Sellers_EU с перечнем продавцов нашего товара в Европе и нам нужно их добавить в общую таблицу Sellers. Структура этих таблиц одинакова (то же количество колонок и те же их названия), однако другие данные. Для этого мы можем прописать следующий запрос:

INSERT INTO Sellers (ID, Address, City, Seller_name, Country) SELECT ID, Address, City, Seller_name, Country FROM Sellers_EU

Нужно обратить внимание, чтобы значение внутренних ключей не повторялись (поле ID), в противном случае произойдет ошибка. Оператор SELECT также может включать предложения WHERE для фильтрации данных. Также следует отметить, что СУБД не обращает внимания на названия колонок, которые содержатся в операторе SELECT, для нее важно только порядок их расположения. Поэтому данные в первом указанном столбце, что были выбраны из-за SELECT, будут в любом случае заполнены в первый столбец таблицы Sellers, указанной после оператора INSERT INTO, независимо от названия поля.

4. Копирование данных из одной таблицы в другую

Часто при работе с базами данных возникает необходимость в создании копий любых таблиц, с целью резервирования или модификации. Чтобы сделать полную копию таблицы в SQL предусмотрен отдельный оператор SELECT INTO. Например, нам нужно создать копию таблицы Sellers, нужно будет прописать запрос следующим образом:

SELECT * INTO Sellers_new FROM Sellers

В отличие от предыдущей конструкции INSERT INTO … SELECT … , когда данные добавляются в существующую таблицу, конструкция SELECT … INTO … FROM … копирует данные в новую таблицу. Также можно сказать, что первая конструкция импортирует данные, а вторая — экспортирует. При использовании конструкции SELECT … INTO … FROM … следует учитывать следующее:

• — можно использовать любые предложения в операторе SELECT, такие как GROUP BY и HAVING
• — для добавления данных из нескольких таблиц можно использовать объединение
• — данные возможно добавить только одну таблицу, независимо от того, из скольких таблиц они были взяты.

Вставка данных только в указанные столбцы

Кроме того, можно вставлять данные только в определенные столбцы.

Следующая инструкция SQL вставит новую запись, но только вставит данные в столбцы «CustomerName», «City» и «Country» (CustomerID будет обновляться автоматически):

Пример

INSERT INTO Customers (CustomerName, City, Country)
VALUES (‘Cardinal’, ‘Stavanger’, ‘Norway’);

Выбор из таблицы «Customers» теперь будет выглядеть следующим образом:

CustomerIDCustomerNameContactNameAddressCityPostalCodeCountry

89	White Clover Markets	Karl Jablonski	305 — 14th Ave. S. Suite 3B	Seattle	98128	USA
90	Wilman Kala	Matti Karttunen	Keskuskatu 45	Helsinki	21240	Finland
91	Wolski	Zbyszek	ul. Filtrowa 68	Walla	01-012	Poland
92	Cardinal	null	null	Stavanger	null	Norway

SQL INSERT INTO SELECT

Инструкция INSERT INTO SELECT копирует данные из одной таблицы и вставляет их в другую таблицу.

• INSERT INTO SELECT требует, чтобы типы данных в исходной и целевой таблицах совпадали
• Существующие записи в целевой таблице остаются неизменными

Синтаксис INSERT INTO SELECT

Копирование всех столбцов из одной таблицы в другую таблицу:

INSERT INTO table2
SELECT * FROM table1
WHERE condition;

Скопируйте только некоторые столбцы из одной таблицы в другую таблицу:

INSERT INTO table2 (column1, column2, column3, …)
SELECT column1, column2, column3, …
FROM table1
WHERE condition;

Демо база данных

В этом уроке мы будем использовать хорошо известный пример базы данных Northwind.

Ниже приведен выбор из таблицы «Customers»:

CustomerIDCustomerNameContactNameAddressCityPostalCodeCountry

1	Alfreds Futterkiste	Maria Anders	Obere Str. 57	Berlin	12209	Germany
2	Ana Trujillo Emparedados y helados	Ana Trujillo	Avda. de la Constitución 2222	México D.F.	05021	Mexico
3	Antonio Moreno Taquería	Antonio Moreno	Mataderos 2312	México D.F.	05023	Mexico

И выбор из таблицы «Suppliers»:

SupplierIDSupplierNameContactNameAddressCityPostal CodeCountry

1	Exotic Liquid	Charlotte Cooper	49 Gilbert St.	Londona	EC1 4SD	UK
2	New Orleans Cajun Delights	Shelley Burke	P.O. Box 78934	New Orleans	70117	USA
3	Grandma Kelly’s Homestead	Regina Murphy	707 Oxford Rd.	Ann Arbor	48104	USA

Примеры SQL INSERT INTO SELECT

Следующая инструкция SQL копирует «Suppliers» в «Customers» (столбцы, которые не заполнены данными, будут содержать NULL):

Пример

INSERT INTO Customers (CustomerName, City, Country)
SELECT SupplierName, City, Country FROM Suppliers; Попробуйте сами »

Следующая инструкция SQL копирует «Suppliers» в «Customers» (заполните все столбцы):

Пример

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
SELECT SupplierName, ContactName, Address, City, PostalCode, Country FROM Suppliers; Попробуйте сами »

Следующая инструкция SQL копирует только немецких поставщиков в раздел «Customers»:

Пример

INSERT INTO Customers (CustomerName, City, Country)
SELECT SupplierName, City, Country FROM Suppliers
WHERE Country=’Germany’; Попробуйте сами