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

Область применения: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure

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

Примечание.

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

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

• DELETE
• INSERT
• UPDATE
• MERGE

Соглашения о синтаксисе 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

Аргументы

@table_variable

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

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

Дополнительные сведения о переменных таблицы см. в таблице.

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 отражением значения до DELETEUPDATEзавершения инструкции или MERGE инструкции.

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

INSERTED

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

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

from_table_name

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

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

*

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

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

DELETE Sales.ShoppingCartItem
    OUTPUT DELETED.*;

column_name

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

$action

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

Замечания

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

Примечание.

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

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

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

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

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

Можно использовать OUTPUT с оператором или DELETE операторомUPDATE, расположенным на курсоре, использующим WHERE CURRENT OF синтаксис.

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

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

• INSERT операторы, содержащие инструкцию EXECUTE .

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

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

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

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

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

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

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

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

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

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

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

При захвате результатов предложения в вложенных INSERT, UPDATEDELETEили 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) указание для инструкции, а также используется OUTPUT...INTO для SELECT вставки во временную или пользовательную таблицу, целевая таблица для нее INSERT...SELECT может быть параллелизмом в зависимости от стоимости поддерев. Целевая таблица, указанная в OUTPUT INTO предложении, не подходит для параллелизма.

Триггеры

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

Для INSTEAD OF триггеров возвращаемые результаты создаются так же, как будто INSERTUPDATEпроизошло или DELETE произошло, даже если в результате операции триггера не произошло никаких изменений. Если инструкция, содержащая OUTPUT предложение, используется внутри текста триггера, псевдонимы таблиц должны использоваться для ссылки на вставленные и удаленные таблицы триггера, чтобы избежать дублирования ссылок на столбцы с таблицами, DELETED связанными с INSERTED 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

Примечание.

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

Разрешения

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

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

Примеры

Примеры кода Transact-SQL в этой статье используют AdventureWorks2022 базу данных или AdventureWorksDW2022 пример базы данных, которую можно скачать с домашней страницы примеров и проектов сообщества Microsoft SQL Server.

А. Использование 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в столбце Production.Document nvarchar(max) таблицы с помощью .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.ProductPhotoID и дату и время удаления строки из ProductProductPhoto таблицы в DELETED.ProductIDвызывающее приложение.

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

Связанный контент

• DELETE (Transact-SQL)
• INSERT (Transact-SQL)
• UPDATE (Transact-SQL)
• table (Transact-SQL)
• CREATE TRIGGER (Transact-SQL)
• sp_configure (Transact-SQL)