Инструкция OUTPUT в Transact-SQL – примеры использования

В языке Transact-SQL инструкции по модификации данных, такие как INSERT, UPDATE, DELETE и MERGE, не возвращают данные о том, какие именно строки были затронуты, т.е. изменены, однако с помощью указания дополнительной инструкции OUTPUT мы можем это узнать. В этой статье мы рассмотрим инструкцию OUTPUT и разберем несколько примеров ее использования.

OUTPUT в T-SQL

OUTPUT – это инструкция, которая возвращает изменившиеся строки в результате выполнения инструкций INSERT, UPDATE, DELETE или MERGE.

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

Принцип работы OUTPUT заключается в следующем: все изменения, которые производят инструкции INSERT, UPDATE, DELETE и MERGE, фиксируются, условно говоря, во временных таблицах Inserted и Deleted. Они имеют точно такую же структуру, как и целевая таблица, таким образом, для того чтобы посмотреть изменения, нам необходимо просто в инструкции OUTPUT указать соответствующий префикс и название нужного столбца, примерно так же, как мы это делаем в инструкции SELECT, перечисляя названия столбцов, тем самым мы извлечем данные из этих таблиц.

• Префикс Inserted – указывается для того, чтобы получить добавленные строки, или новые значения в случае с обновлением.
• Префикс Deleted – указывается для получения удаленных строк, или для старых значений в случае с обновлением данных.

Примеры использования инструкции OUTPUT в T-SQL

Сейчас мы разберем примеры использования инструкции OUTPUT в сочетании с INSERT, UPDATE и DELETE, пример с MERGE мы подробно рассматривали в материале – Операция MERGE в языке Transact-SQL – описание и примеры, поэтому здесь я не буду повторяться.

Исходные данные для примеров

Сначала давайте создадим тестовые данные, а именно таблицу, с названием TestTable, именно данные в ней мы и будем изменять.

В качестве сервера у меня выступает Microsoft SQL Server 2016 Express.

   
   --Создание таблицы
   CREATE TABLE TestTable(
     [ProductId]     [INT] IDENTITY(1,1) NOT NULL,
     [CategoryId]    [INT] NOT NULL,
     [ProductName]   [VARCHAR](100) NOT NULL,
     [Price]         [Money] NULL
   )
   GO

Пример использования OUTPUT в сочетании с INSERT

В этом примере мы добавим три строки и сразу вернем результат (добавленные строки) инструкцией OUTPUT, для этого мы обратимся к таблице Inserted (т.е. укажем префикс).

Инструкция OUTPUT указывается после инструкции INSERT и определения целевой таблицы.

  
   INSERT INTO TestTable
    OUTPUT Inserted.ProductId,
           Inserted.CategoryId,
           Inserted.ProductName,
           Inserted.Price
    VALUES (1, 'Клавиатура', 150),
           (1, 'Мышь', 50),
           (2, 'Телефон', 300)

Пример использования OUTPUT в сочетании с UPDATE

В случае с обновлением данных (UPDATE) мы уже можем обращаться и к Inserted и к Deleted, для того чтобы получить как новые, так и старые значения. OUTPUT также указывается после модифицирующей инструкции, в этом случае после UPDATE, стоит отметить, что условие WHERE мы пишем после инструкции OUTPUT.

   
   UPDATE TestTable SET Price = Price + 10
     OUTPUT Inserted.ProductId AS [ProductId],
            Deleted.Price AS [Старое значение Price],
            Inserted.Price AS [Новое значение Price]
     WHERE Price < 200

Пример использования OUTPUT в сочетании с UPDATE и конструкцией INTO

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

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

   
   --Объявление табличной переменной
   DECLARE @TmpTable TABLE (ProductId INT, PriceOld Money, PriceNew Money);

   --Выполнение UPDATE с инструкцией OUTPUT
   UPDATE TestTable SET Price = Price + 10
        OUTPUT Inserted.ProductId AS [ProductId],
               Deleted.Price AS [Старое значение Price],
               Inserted.Price AS [Новое значение Price]
        INTO @TmpTable (ProductId, PriceOld, PriceNew) --Сохраняем результат в табличной переменной
   WHERE Price < 200

   --Можем анализировать сохраненные данные
   SELECT * FROM @TmpTable

Пример использования OUTPUT в сочетании с DELETE

Если в инструкции OUTPUT обратиться к таблицам Inserted или Deleted указав *, то, как и в случае с SELECT, выведутся все столбцы таблицы. В следующем примере, для того чтобы посмотреть значения всех столбцов удаленных строк, указан символ *.

   
   DELETE TestTable
       OUTPUT Deleted.*
   WHERE ProductId < 3

Инструкцию OUTPUT языка T-SQL мы рассмотрели, надеюсь, материал был Вам полезен. Всем тем, кто только начинает свое знакомство с языком SQL, рекомендую прочитать книгу «SQL код» – это самоучитель по языку SQL, которую написал я, и в которой я подробно, и в то же время простым языком, рассказываю о языке SQL, удачи!

Заметка! Для комплексного изучения языка SQL и T-SQL рекомендую посмотреть мои видеокурсы по T-SQL, в которых используется последовательная методика обучения специально для начинающих.