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

На рис. 6.6. представлен примеры нескольких операторов INSERT, применяющих список столбцов. Обратите внимание: ключевое слово INTO при написании оператора INSERT не является обязательным и может быть опущено.

Рис. 6.6. Примеры оператора INSERT со списком столбцов

В каждом операторе INSERT на рис. 6.6 применяется разный список столбцов. Кроме того, в первом операторе не предполагается ввод значения в столбец GENDER (его нет в списке столбцов), поэтому введется значение NULL или значение по умолчанию, в третьем операторе явно указывается, что в столбец вводится значение по умолчанию.

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

6.4. Конструкция INSERT … SELECT

В качестве таблицы-источника может выступать результирующий набор, сформированный оператором SELECT. На рис. 6.7 представлен многотабличный SELECT-запрос (детально такие запросы будут рассматриваться позже). Результирующий набор, сформированный после выполнения запроса (на рис. 6.7 представлен его фрагмент), содержит данные из трех таблиц: STUDENT, SUBJECT и PROGRESS.

Рис. 6.7. Многотабличный SELECT-запрос и результирующий набор,
 сформированный по итогам его выполнения

Иногда для последующей обработки выбранных данных бывает полезным сохранить их во временной таблице. На рис. 6.8 представлен сценарий, в котором создается временная таблица с именем [#Результаты экзамена], и с помощью оператора INSERT результат SELECT-запроса (рис. 6.7) помещается в эту таблицу. Следует обратить внимание, что для таблицы-приемника используется форма, подразумевающая стандартный порядок столбцов. На рис. 6.9 представлен SELECT-запрос к таблице [#Результаты экзамена], а также результат выполнения этого запроса. Не сложно убедиться, что соответствие между столбцами результирующего набора и столбами таблицы [#Результаты экзамена] устанавливается по порядку их перечисления.

Рис. 6.8. Оператор INSERT, помещающий результат SELECT-запроса
 во временную таблицу

Рис. 6.9. SELECT-запрос к временной таблице [#Результаты экзамена]
и результат его выполнения

При необходимости можно применить список столбцов для того, чтобы обеспечить соответствие порядку столбцов, указанному в SELECT-запросе. На рис. 6.10 приведен пример оператора INSERT со списком столбцов. Обратите внимание, что столбцы в SELECT-запросе переставлены в другом порядке, нежели в запросе на рис. 6.8, а порядок столбцов, указанный в INSERT, соответствует этому порядку.

Рис. 6.10. Пример оператора INSERTсо списком столбцов

При добавлении строк с помощью конструкции INSERT … SELECT можно ограничить количество добавляемых строк с помощью необязательной секции TOP, которая располагается сразу за ключевым словом INSERT(рис. 6.11).

Рис. 6.11. Пример использования секции TOP в операторе INSERT

В примере на рис. 6.11 в операторе INSERT используется секция TOP, которая задает количество строк (в наше случае 7), добавляемых в таблицу [#Результаты экзамена]. Надо обратить внимание на то, что TOP отбирает из результирующего набора только заданное количество первых строк (в нашем случае 7 первых строк), поэтому важен порядок сортировки, указанный в операторе SELECT с помощью секции ORDER BY (подробно применение секции ORDER BY будет рассматриваться позже).

6.5. Конструкция INSERT … EXECUTE

SELECT-запросы, используемые в операторе INSERT, могут быть достаточно сложными и, кроме того, могут применяться и в других местах. Поэтому важно иметь механизм, позволяющий сохранить однажды написанный запрос для его повторного применения. Microsoft SQL Server 2008 имеет несколько таких механизмов: представления, хранимые процедуры и функции – все они будут подробно рассматриваться в этом пособии позже.

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

В первом приближении хранимая процедура – объект БД, представляющий собой поименованный код, написанный на T-SQL, который может принимать параметры, возвращать параметры и формировать результирующие наборы. В нашем случае интерес представляет именно последняя из перечисленных возможностей хранимой процедуры – способность формировать результирующие наборы.

На рис. 6.12 приведен пример сценария, в котором сначала создается хранимая процедура GetExamResults с помощью оператора CREATE PROCEDURE, а затем вызывается с помощью оператора EXECUTE.

Рис. 6.12. Пример создания и вызова процедуры, формирующей
 результирующий набор

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

На рис. 6.13 приведен сценарий, демонстрирующий применение конструкции INSERT … EXECUTE.

Рис. 6.13. Пример использования конструкции INSERT … EXECUTE
с вызовом хранимой процедуры, созданной сценарием на рис. 6.12

Результат выполнения оператора INSERT на рис. 6.13 будет точно такой же, как при выполнении оператора INSERT в сценарии на рис. 6.8.

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

Применение секции OUTPUT

Операторы INSERT обновляют БД и поэтому часто являются частью транзакции. Механизм транзакций рассматривается в этом пособии позже, но здесь поясним, что транзакция в первом приближении – это набор операторов DML SQL, которые рассматриваются как единое целое и могут либо все полностью выполниться (и изменить БД) или все не выполниться (и БД не изменится), причем ключевое слово здесь «все».

Если разработчик БД собирается фиксировать DML-события (INSERT, UPDATE, DELETE), изменяющие БД (например, записывать информацию об изменениях в БД), то он должен в рамках изменяющей БД транзакции записывать эту информацию (обычно в другую таблицу, называемую журналом). Однако если транзакция не завершится успешно, то все изменения БД, которые были выполнены в рамках транзакции, аннулируются, в том числе данные, записанные в журнал. Таким образом, в журнале будет сохраняться информация только об успешных изменениях БД (в случае успешного завершения транзакции), а информация о неуспешных попытках изменить БД в журнале не сохранится, т. к. сама запись в журнал является частью транзакции, изменяющей БД.

Секция OUTPUT может использоваться во всех трех DML-операторах, изменяющих БД: INSERT, DELETE, UPDATE. Главной особенностью этой секции является то, что на ее работу не распространяется действие процедуры отката (ROLLBACK), устраняющей все изменения БД, выполненные DML-операторами в рамках транзакции в случае ее неуспешного завершения.

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

На рис. 6.14 представлен пример использования оператора INSERT с секцией OUTPUT, предназначенной для вывода информации в результирующий набор.

Рис. 6.14. Пример использования секции OUTPUT в операторе INSERT

Следует обратить внимание на префикс INSERTED, используемый в списке OUTPUT. Этот префикс обозначает, что далее через точку следует имя столбца таблицы, в которую добавляется строка.

Результирующий набор можно направить в предварительно созданную таблицу. На рис. 6.15 приведен пример оператора INSERT, использующий секцию OUTPUT для вывода информации в предварительно созданную временную таблицу [#Журнал]. Содержимое таблицы [#Журнал] выводится с помощью оператора SELECT в сценарии на рис. 6.16.

Рис. 6.15. Пример использования в операторе INSERT секции OUTPUT,
записывающей данные в таблицу

Рис. 6.16. Содержимое таблицы [#Журнал], сформированное
секцией OUTPUT оператора INSERT в сценарии на рис. 6.15

Применение секции OUPUT не допускается в конструкции INSERT … EXECUTE, требует списка вводимых столбцов, а также накладывает ряд ограничений на таблицу, в которую секция OUTPUT записывает информацию. Более детально с применением секции OUTPUT можно ознакомиться в издании [5].