Предложение 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