Предложение OUTPUT (Transact-SQL)

  • Статья

Применимо к:SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure

Возвращает данные из строк, изменившихся в результате выполнения инструкций INSERT, UPDATE, DELETE или MERGE, или выражения на основе этих данных. Эти результаты могут быть возвращены приложению, например для вывода подтверждающих сообщений, архивирования и т. п. Результаты также могут быть вставлены в таблицу или табличную переменную. Кроме того, можно записать результаты предложения в вложенных инструкциях INSERT, UPDATE, DELETE или MERGE и вставить эти результаты OUTPUT в целевую таблицу или представление.

Примечание.

Инструкция UPDATE, INSERT или DELETE с OUTPUT предложением возвращает строки клиенту, даже если инструкция сталкивается с ошибками и откатывается. Результат не следует использовать, если при запуске инструкции возникает какая-либо ошибка.

Применяется в:

Соглашения о синтаксисе Transact-SQL

Синтаксис

<OUTPUT_CLAUSE> ::=
{
    [ OUTPUT <dml_select_list> INTO { @table_variable | output_table } [ ( column_list ) ] ]
    [ OUTPUT <dml_select_list> ]
}
<dml_select_list> ::=
{ <column_name> | scalar_expression } [ [ AS ] column_alias_identifier ]
    [ , ...n ]

<column_name> ::=
{ DELETED | INSERTED | from_table_name } . { * | column_name }
    | $action

Примечание.

Сведения о синтаксисе Transact-SQL для SQL Server 2014 (12.x) и более ранних версиях см . в документации по предыдущим версиям.

Аргументы

@table_variable

Указывает переменную table, в которую возвращенные строки вставляются вместо передачи вызывающему приложению. @table_variable необходимо объявить перед инструкцией INSERT, UPDATE, DELETE или MERGE.

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

Дополнительные сведения о переменных типа table см. в статье table (Transact-SQL).

output_table

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

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

output_table не удается:

  • Иметь включенные триггеры, определенные для нее.
  • Участвовать в ограничениях FOREIGN KEY с любой стороны.
  • Иметь ограничения CHECK или активированные правила.

column_list

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

scalar_expression

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

Ссылки на изменяемые столбцы таблицы должны предваряться префиксом INSERTED или DELETED.

column_alias_identifier

Альтернативное имя, используемое для ссылки на имя столбца.

DELETED

Префикс столбца, указывающий значение, удаленное операцией обновления или удаления. Столбцы, имеющие префикс DELETED, отражают значение, которое было до завершения инструкции UPDATE, DELETE или MERGE.

DELETED нельзя использовать с предложением OUTPUT в инструкции INSERT.

INSERTED

Префикс столбца, указывающий значение, добавленное операцией вставки или обновления. Столбцы, имеющие префикс INSERTED, отражают значение, полученное после завершения инструкции UPDATE, INSERT или MERGE, но до выполнения триггеров.

INSERTED нельзя использовать с предложением OUTPUT в инструкции DELETE.

from_table_name

Префикс столбца, указывающий таблицу, включенную в предложение FROM инструкции DELETE, UPDATE или MERGE, которая используется для указания строк для обновления или удаления.

Если изменяемая таблица указана также и в предложении FROM, все ссылки на столбцы, содержащиеся в этой таблице, должны предваряться префиксом INSERTED или DELETED.

*

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

Например, в следующей инструкции DELETE предложение OUTPUT DELETED.* возвращает все столбцы, удаленные из таблицы ShoppingCartItem:

DELETE Sales.ShoppingCartItem
    OUTPUT DELETED.*;

column_name

Явная ссылка на столбец. Любое указание столбцов в изменяемой таблице должно предваряться соответствующим префиксом INSERTED или DELETED, например: INSERTED.имя_столбца.

$action

Доступно только для инструкции MERGE. Указывает столбец типа nvarchar(10) в предложении в OUTPUT инструкции MERGE, которая возвращает одно из трех значений для каждой строки: INSERT, UPDATE или DELETE, в соответствии с действием, которое было выполнено в этой строке.

Замечания

Предложение OUTPUT <dml_select_list> и OUTPUT <dml_select_list> INTO { @table_variable | output_table } предложение можно определить в одной инструкции INSERT, UPDATE, DELETE или MERGE.

Примечание.

Если не указано иное, ссылки на OUTPUT предложение относятся как к предложению, так OUTPUT и к предложению OUTPUT INTO .

Предложение OUTPUT может быть полезно для получения значения удостоверений или вычисляемых столбцов после операции INSERT или UPDATE.

Если вычисляемый столбец включен в <dml_select_list>столбец, соответствующий столбец в выходной таблице или переменной таблицы не является вычисляемой. В него будет помещено значение, вычисленное в момент выполнения инструкции.

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

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

С инструкцией UPDATE или DELETE, OUTPUT размещенной на курсоре, использующим синтаксис WHERE CURRENT OF.

Предложение OUTPUT не поддерживается в следующих инструкциях:

  • Инструкций DML, которые содержат ссылки на локальные секционированные представления, распределенные секционированные представления или удаленные таблицы.

  • Инструкций INSERT, содержащих инструкции EXECUTE.

  • Полнотекстовые предикаты не допускаются в OUTPUT предложении, если для уровня совместимости базы данных задано значение 100.

  • Предложение OUTPUT INTO нельзя использовать для вставки в представление или функцию набора строк.

  • Определяемая пользователем функция не может быть создана, если она содержит OUTPUT предложение INTO, содержащее таблицу в качестве целевой.

Чтобы предотвратить недетерминированное поведение, OUTPUT предложение не может содержать следующие ссылки:

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

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

    • вложенный запрос.

    • Определяемая пользователем функция, которая осуществляет или может осуществлять доступ к пользовательским или системным данным.

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

    Когда SQL Server обнаруживает такой столбец в OUTPUT предложении, возникает ошибка 4186.

Вставка данных из предложения OUTPUT в таблицу

При записи результатов предложения в вложенных инструкциях INSERT, UPDATE, DELETE или MERGE и вставке этих результатов OUTPUT в целевую таблицу следует учитывать следующие сведения:

  • Вся операция является атомарной. Либо инструкция INSERT, так и вложенный оператор DML, содержащий OUTPUT предложение, или весь оператор завершается ошибкой.

  • К целевому объекту внешней инструкции INSERT применяются следующие ограничения.

    • Целевой объект не может быть удаленной таблицей, представлением или общим выражением таблицы.

    • Целевой объект не может иметь ограничение FOREIGN KEY или ссылаться на ограничение FOREIGN KEY.

    • Триггеры не могут быть определены в целевом объекте.

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

  • К вложенной инструкции DML применяются следующие ограничения.

    • Целевой объект не может быть удаленной таблицей или секционированного представления.

    • Сам источник не может содержать <dml_table_source> предложение.

  • Предложение OUTPUT INTO не поддерживается в инструкциях INSERT, содержащих <dml_table_source> предложение.

  • @@ROWCOUNT возвращает строки, вставляемые только внешней инструкцией INSERT.

  • @@IDENTITY, SCOPE_IDENTITYи IDENT_CURRENT возвращать значения удостоверений, созданные только вложенным оператором DML, а не внешним оператором INSERT.

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

  • <dml_table_source> В предложении предложения SELECT и WHERE не могут включать вложенные запросы, агрегатные функции, функции ранжирования, полнотекстовые предикаты, определяемые пользователем функции, которые выполняют доступ к данным или TEXTPTR() функцию.

Параллелизм

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

В контексте набора базы данных уровня совместимости 130 или более поздней версии, если INSERT...SELECT операция использует WITH (TABLOCK) указание инструкции SELECT, а также используется OUTPUT...INTO для вставки во временную или пользовательную таблицу, целевая таблица для нее INSERT...SELECT будет иметь право на параллелизм в зависимости от стоимости поддерев. Целевая таблица, на которую ссылается предложение OUTPUT INTO , не будет иметь права на параллелизм.

Триггеры

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

Для триггеров INSTEAD OF возвращенные результаты формируются таким образом, как если бы операции INSERT, UPDATE или DELETE были действительно выполнены, даже если в результате выполнения триггера никакие реальные изменения данных не произведены. Если инструкция, содержащая OUTPUT предложение, используется внутри текста триггера, псевдонимы таблиц должны использоваться для ссылки на вставленные и удаленные таблицы триггера, чтобы избежать дублирования ссылок на столбцы с таблицами INSERTED и DELETED, связанными с OUTPUT.

OUTPUT Если предложение указано без указания ключевое слово INTO, целевой объект операции DML не может иметь для него триггер с включенным триггером для заданного действия DML. Например, если OUTPUT предложение определено в инструкции UPDATE, целевая таблица не может иметь триггеров UPDATE.

sp_configure Если параметр запрещает результаты триггеров, OUTPUT предложение без предложения INTO приводит к сбою инструкции при вызове из триггера.

Типы данных

Предложение OUTPUT поддерживает типы данных больших объектов: nvarchar(max), varchar(max),varbinary(max), text, ntext, image и xml. При использовании .WRITE предложения в инструкции UPDATE для изменения столбца nvarchar(max), varchar(max)или varbinary(max) возвращается полное и после получения изображений значений, если они ссылаются. Функция TEXTPTR() не может отображаться как часть выражения в тексте, ntext или столбце изображения в предложении OUTPUT .

Очереди

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

USE AdventureWorks2022;
GO

DELETE TOP(1) dbo.DatabaseLog WITH (READPAST)
OUTPUT DELETED.*
WHERE DatabaseLogID = 7;
GO

В этом примере строка удаляется из таблицы, используемой в качестве очереди, и удаляемое значение возвращается приложению. Можно реализовать также и другую семантику, например применение таблицы как стека. Однако SQL Server не гарантирует порядок обработки и возврата строк с помощью инструкций DML с помощью OUTPUT предложения. Приложение должно указать соответствующее предложение WHERE, гарантирующее желаемую семантику, либо ориентироваться на то, что порядок, в котором строки обрабатываются операцией DML, не гарантируется. В следующем примере для реализации порядка обработки строк применяется вложенный запрос, который предполагает уникальность значений в столбце DatabaseLogID.

USE tempdb;
GO

CREATE TABLE dbo.table1
(
    id INT,
    employee VARCHAR(32)
);
GO
  
INSERT INTO dbo.table1
VALUES (1, 'Fred'),
    (2, 'Tom'),
    (3, 'Sally'),
    (4, 'Alice');
GO
  
DECLARE @MyTableVar TABLE (
    id INT,
    employee VARCHAR(32)
);

PRINT 'table1, before delete';

SELECT *
FROM dbo.table1;

DELETE
FROM dbo.table1
OUTPUT DELETED.*
INTO @MyTableVar
WHERE id = 4
    OR id = 2;

PRINT 'table1, after delete';

SELECT *
FROM dbo.table1;

PRINT '@MyTableVar, after delete';

SELECT *
FROM @MyTableVar;

DROP TABLE dbo.table1;

Результаты приведены ниже.

table1, before delete
id          employee
----------- ------------------------------
1           Fred
2           Tom
3           Sally
4           Alice

table1, after delete
id          employee
----------- ------------------------------
1           Fred
3           Sally

@MyTableVar, after delete
id          employee
----------- ------------------------------
2           Tom
4           Alice

Примечание.

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

Разрешения

Необходимы разрешения SELECT на все столбцы, полученные через <dml_select_list> или указанные в <scalar_expression>.

Разрешения INSERT необходимы для всех таблиц, указанных в <output_table>.

Примеры

А. Использование OUTPUT INTO с инструкцией INSERT

В следующем примере производится вставка строки в таблицу ScrapReason, а затем при помощи предложения OUTPUT результаты выполнения инструкции возвращаются в табличную переменную @MyTableVar. ScrapReasonID Так как столбец определен со свойством IDENTITY, значение не указано в INSERT инструкции для этого столбца. Однако значение, созданное ядро СУБД для этого столбца, возвращается в OUTPUT предложении в столбцеINSERTED.ScrapReasonID.

USE AdventureWorks2022;
GO

DECLARE @MyTableVar TABLE (
    NewScrapReasonID SMALLINT,
    Name VARCHAR(50),
    ModifiedDate DATETIME);
    
INSERT Production.ScrapReason
    OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, INSERTED.ModifiedDate
        INTO @MyTableVar
VALUES (N'Operator error', GETDATE());
  
--Display the result set of the table variable.
SELECT NewScrapReasonID, Name, ModifiedDate FROM @MyTableVar;
--Display the result set of the table.
SELECT ScrapReasonID, Name, ModifiedDate
FROM Production.ScrapReason;
GO

B. Использование OUTPUT с инструкцией DELETE

В следующем примере производится удаление всех строк из таблицы ShoppingCartItem. OUTPUT DELETED.* Предложение указывает, что результаты DELETE инструкции, то есть все столбцы в удаленных строках, возвращаются вызывающей приложению. Следующая инструкция SELECT проверяет результаты операции удаления из таблицы ShoppingCartItem.

USE AdventureWorks2022;
GO

DELETE Sales.ShoppingCartItem
OUTPUT DELETED.*
WHERE ShoppingCartID = 20621;
  
--Verify the rows in the table matching the WHERE clause have been deleted.
SELECT COUNT(*) AS [Rows in Table] FROM Sales.ShoppingCartItem WHERE ShoppingCartID = 20621;
GO

C. Использование OUTPUT INTO с инструкцией UPDATE

В следующем примере в столбце VacationHours для первых 10 строк таблицы Employee устанавливается значение 25 %. Предложение OUTPUT возвращает значение VacationHours, существующее до применения инструкции UPDATE в столбце DELETED.VacationHours, и обновленное значение в столбце INSERTED.VacationHours к табличной переменной @MyTableVar.

Ниже приведены две SELECT инструкции, возвращающие значения @MyTableVar и результаты операции обновления в Employee таблице.

USE AdventureWorks2022;
GO
  
DECLARE @MyTableVar TABLE (
    EmpID INT NOT NULL,
    OldVacationHours INT,
    NewVacationHours INT,
    ModifiedDate DATETIME);
  
UPDATE TOP (10) HumanResources.Employee
SET VacationHours = VacationHours * 1.25,
    ModifiedDate = GETDATE()
OUTPUT INSERTED.BusinessEntityID,
       DELETED.VacationHours,
       INSERTED.VacationHours,
       INSERTED.ModifiedDate
INTO @MyTableVar;
  
--Display the result set of the table variable.
SELECT EmpID, OldVacationHours, NewVacationHours, ModifiedDate
FROM @MyTableVar;
GO
--Display the result set of the table.
SELECT TOP (10) BusinessEntityID, VacationHours, ModifiedDate
FROM HumanResources.Employee;
GO

D. Использование OUTPUT INTO для возврата выражения

Следующий пример, основанный на предыдущем примере, определяет выражение в предложении OUTPUT как разницу между обновленным значением VacationHours и значением VacationHours до применения операции обновления. Значение этого выражения возвращается в табличную переменную @MyTableVar в столбце VacationHoursDifference.

USE AdventureWorks2022;
GO

DECLARE @MyTableVar TABLE (
    EmpID INT NOT NULL,
    OldVacationHours INT,
    NewVacationHours INT,
    VacationHoursDifference INT,
    ModifiedDate DATETIME);
  
UPDATE TOP (10) HumanResources.Employee
SET VacationHours = VacationHours * 1.25,
    ModifiedDate = GETDATE()
OUTPUT INSERTED.BusinessEntityID,
       DELETED.VacationHours,
       INSERTED.VacationHours,
       INSERTED.VacationHours - DELETED.VacationHours,
       INSERTED.ModifiedDate
INTO @MyTableVar;
  
--Display the result set of the table variable.
SELECT EmpID, OldVacationHours, NewVacationHours,
    VacationHoursDifference, ModifiedDate
FROM @MyTableVar;
GO
SELECT TOP (10) BusinessEntityID, VacationHours, ModifiedDate
FROM HumanResources.Employee;
GO

Д. Использование OUTPUT INTO с from_table_name в инструкции UPDATE

В приведенном ниже примере производится обновление столбца ScrapReasonID таблицы WorkOrder для всех заказов на производство с указанными значениями ProductID и ScrapReasonID. Предложение OUTPUT INTO возвращает значения из обновляемой таблицы (WorkOrder), а также из таблицы Product. Таблица Product в предложении FROM указывает, какие строки следует обновлять. Для таблицы WorkOrder определен триггер AFTER UPDATE, поэтому требуется ключевое слово INTO.

USE AdventureWorks2022;
GO

DECLARE @MyTestVar TABLE (
    OldScrapReasonID INT NOT NULL,
    NewScrapReasonID INT NOT NULL,
    WorkOrderID INT NOT NULL,
    ProductID INT NOT NULL,
    ProductName NVARCHAR(50)NOT NULL);
  
UPDATE Production.WorkOrder
SET ScrapReasonID = 4
OUTPUT DELETED.ScrapReasonID,
       INSERTED.ScrapReasonID,
       INSERTED.WorkOrderID,
       INSERTED.ProductID,
       p.Name
    INTO @MyTestVar
FROM Production.WorkOrder AS wo
    INNER JOIN Production.Product AS p
    ON wo.ProductID = p.ProductID
    AND wo.ScrapReasonID= 16
    AND p.ProductID = 733;
  
SELECT OldScrapReasonID, NewScrapReasonID, WorkOrderID,
    ProductID, ProductName
FROM @MyTestVar;
GO

F. Использование OUTPUT INTO с from_table_name в инструкции DELETE

В следующем примере производится удаление строк из таблицы ProductProductPhoto на основе критерия поиска, определенного в предложении FROM инструкции DELETE. Предложение OUTPUT возвращает столбцы из таблицы, из которой производится удаление (DELETED.ProductID, DELETED.ProductPhotoID) и столбцы из таблицы Product. Эта таблица, указанная в предложении FROM, определяет, какие строки следует удалять.

USE AdventureWorks2022;
GO

DECLARE @MyTableVar TABLE (
    ProductID INT NOT NULL,
    ProductName NVARCHAR(50)NOT NULL,
    ProductModelID INT NOT NULL,
    PhotoID INT NOT NULL);
  
DELETE Production.ProductProductPhoto
OUTPUT DELETED.ProductID,
       p.Name,
       p.ProductModelID,
       DELETED.ProductPhotoID
    INTO @MyTableVar
FROM Production.ProductProductPhoto AS ph
JOIN Production.Product as p
    ON ph.ProductID = p.ProductID
    WHERE p.ProductModelID BETWEEN 120 and 130;
  
--Display the results of the table variable.
SELECT ProductID, ProductName, ProductModelID, PhotoID
FROM @MyTableVar
ORDER BY ProductModelID;
GO

G. Использование OUTPUT INTO с типом данных большого объекта

В следующем примере для обновления части значения в столбце DocumentSummary, имеющем тип nvarchar(max) в таблице Production.Document, используется предложение .WRITE. Слово components заменяется словом features, при этом указывается новое слово, начальное смещение слова, заменяемого в исходном тексте и число заменяемых символов (длина). В примере предложение OUTPUT возвращает образы столбца DocumentSummary до и после изменения в табличную переменную @MyTableVar. Возвращаются полные изображения столбца DocumentSummary до и после них.

USE AdventureWorks2022;
GO

DECLARE @MyTableVar TABLE (
    SummaryBefore NVARCHAR(max),
    SummaryAfter NVARCHAR(max));
  
UPDATE Production.Document
SET DocumentSummary .WRITE (N'features',28,10)
OUTPUT DELETED.DocumentSummary,
       INSERTED.DocumentSummary
    INTO @MyTableVar
WHERE Title = N'Front Reflector Bracket Installation';
  
SELECT SummaryBefore, SummaryAfter
FROM @MyTableVar;
GO

H. Использование OUTPUT в триггере INSTEAD OF

Следующий пример демонстрирует применение предложения OUTPUT в триггере для возвращения результатов выполнения триггера. Первым делом создается представление для таблицы ScrapReason, затем для этого представления определяется триггер INSTEAD OF INSERT, позволяющий пользователю изменять в базовой таблице только столбец Name. Так как столбец ScrapReasonID базовой таблицы является столбцом IDENTITY, триггер не учитывает значение, предоставленное пользователем. Это позволяет ядро СУБД автоматически создавать правильное значение. Указанное пользователем значение ModifiedDate также не учитывается, и вместо него подставляется текущая дата. Предложение OUTPUT возвращает значения, реально вставленные в таблицу ScrapReason.

USE AdventureWorks2022;
GO

IF OBJECT_ID('dbo.vw_ScrapReason', 'V') IS NOT NULL
    DROP VIEW dbo.vw_ScrapReason;
GO

CREATE VIEW dbo.vw_ScrapReason
AS
SELECT ScrapReasonID,
    Name,
    ModifiedDate
FROM Production.ScrapReason;
GO

CREATE TRIGGER dbo.io_ScrapReason ON dbo.vw_ScrapReason
INSTEAD OF INSERT
AS
BEGIN
    --ScrapReasonID is not specified in the list of columns to be inserted
    --because it is an IDENTITY column.
    INSERT INTO Production.ScrapReason (
        Name,
        ModifiedDate
    )
    OUTPUT INSERTED.ScrapReasonID,
        INSERTED.Name,
        INSERTED.ModifiedDate
    SELECT Name, GETDATE()
    FROM INSERTED;
END
GO

INSERT vw_ScrapReason (
    ScrapReasonID,
    Name,
    ModifiedDate
)
VALUES (
    99,
    N'My scrap reason',
    '20030404'
);
GO

Ниже приведен результирующий набор, полученный 12 апреля 2004 года ('2004-04-12'). Обратите внимание, что столбцы ScrapReasonIDActual и ModifiedDate отражают те значения, которые были получены в результате выполнения триггера, а не те, что были указаны в инструкции INSERT.

ScrapReasonID  Name             ModifiedDate
-------------  ---------------- -----------------------
17             My scrap reason  2004-04-12 16:23:33.050

I. Использование OUTPUT INTO с удостоверениями и вычисляемых столбцов

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

USE AdventureWorks2022;
GO

IF OBJECT_ID('dbo.EmployeeSales', 'U') IS NOT NULL
    DROP TABLE dbo.EmployeeSales;
GO

CREATE TABLE dbo.EmployeeSales (
    EmployeeID INT IDENTITY(1, 5) NOT NULL,
    LastName NVARCHAR(20) NOT NULL,
    FirstName NVARCHAR(20) NOT NULL,
    CurrentSales MONEY NOT NULL,
    ProjectedSales AS CurrentSales * 1.10
);
GO

DECLARE @MyTableVar TABLE (
    EmployeeID INT NOT NULL,
    LastName NVARCHAR(20) NOT NULL,
    FirstName NVARCHAR(20) NOT NULL,
    CurrentSales MONEY NOT NULL,
    ProjectedSales MONEY NOT NULL
);

INSERT INTO dbo.EmployeeSales (
    LastName,
    FirstName,
    CurrentSales
)
OUTPUT INSERTED.EmployeeID,
    INSERTED.LastName,
    INSERTED.FirstName,
    INSERTED.CurrentSales,
    INSERTED.ProjectedSales
INTO @MyTableVar
SELECT c.LastName,
    c.FirstName,
    sp.SalesYTD
FROM Sales.SalesPerson AS sp
INNER JOIN Person.Person AS c
    ON sp.BusinessEntityID = c.BusinessEntityID
WHERE sp.BusinessEntityID LIKE '2%'
ORDER BY c.LastName,
    c.FirstName;

SELECT EmployeeID,
    LastName,
    FirstName,
    CurrentSales,
    ProjectedSales
FROM @MyTableVar;
GO

SELECT EmployeeID,
    LastName,
    FirstName,
    CurrentSales,
    ProjectedSales
FROM dbo.EmployeeSales;
GO

J. Использование OUTPUT и OUTPUT INTO в одной инструкции

В следующем примере производится удаление строк из таблицы ProductProductPhoto на основе критерия поиска, определенного в предложении FROM инструкции DELETE. Предложение OUTPUT INTO возвращает столбцы из удаляемой таблицы (DELETED.ProductID, DELETED.ProductPhotoID) и столбцы из таблицы Product в табличную переменную @MyTableVar. Таблица Product в предложении FROM определяет, какие строки необходимо удалять. Предложение OUTPUT возвращает вызывающему приложению столбцы DELETED.ProductID, DELETED.ProductPhotoID, а также дату и время удаления строки из таблицы ProductProductPhoto.

USE AdventureWorks2022;
GO

DECLARE @MyTableVar TABLE (
    ProductID INT NOT NULL,
    ProductName NVARCHAR(50) NOT NULL,
    ProductModelID INT NOT NULL,
    PhotoID INT NOT NULL
);

DELETE Production.ProductProductPhoto
OUTPUT DELETED.ProductID,
    p.Name,
    p.ProductModelID,
    DELETED.ProductPhotoID
INTO @MyTableVar
OUTPUT DELETED.ProductID,
    DELETED.ProductPhotoID,
    GETDATE() AS DeletedDate
FROM Production.ProductProductPhoto AS ph
INNER JOIN Production.Product AS p
    ON ph.ProductID = p.ProductID
WHERE p.ProductID BETWEEN 800
        AND 810;

--Display the results of the table variable.
SELECT ProductID,
    ProductName,
    PhotoID,
    ProductModelID
FROM @MyTableVar;
GO

K. Вставка данных, возвращаемых из предложения OUTPUT

В следующем примере собираются данные, возвращаемые предложением OUTPUT инструкции MERGE, а затем эти данные вставляются в другую таблицу. Инструкция MERGE ежедневно обновляет столбец Quantity таблицы ProductInventory в соответствии с заказами, обрабатываемыми в таблице SalesOrderDetail. Он также удаляет строки для продуктов, запасы которых удаляются до 0 или меньшего числа. В примере собираются удаленные строки и вставляются в другую таблицу, ZeroInventory, в которой ведется учет закончившихся продуктов.

USE AdventureWorks2022;
GO

IF OBJECT_ID(N'Production.ZeroInventory', N'U') IS NOT NULL
    DROP TABLE Production.ZeroInventory;
GO

--Create ZeroInventory table.
CREATE TABLE Production.ZeroInventory (
    DeletedProductID INT,
    RemovedOnDate DATETIME
    );
GO

INSERT INTO Production.ZeroInventory (
    DeletedProductID,
    RemovedOnDate
)
SELECT ProductID,
    GETDATE()
FROM (
    MERGE Production.ProductInventory AS pi
    USING (
        SELECT ProductID,
            SUM(OrderQty)
        FROM Sales.SalesOrderDetail AS sod
        INNER JOIN Sales.SalesOrderHeader AS soh
            ON sod.SalesOrderID = soh.SalesOrderID
                AND soh.OrderDate = '20070401'
        GROUP BY ProductID
        ) AS src(ProductID, OrderQty)
        ON (pi.ProductID = src.ProductID)
    WHEN MATCHED
        AND pi.Quantity - src.OrderQty <= 0
        THEN
            DELETE
    WHEN MATCHED
        THEN
            UPDATE
            SET pi.Quantity = pi.Quantity - src.OrderQty
    OUTPUT $ACTION,
        DELETED.ProductID
    ) AS Changes(Action, ProductID)
WHERE Action = 'DELETE';

IF @@ROWCOUNT = 0
    PRINT 'Warning: No rows were inserted';
GO

SELECT DeletedProductID,
    RemovedOnDate
FROM Production.ZeroInventory;
GO

Следующие шаги