cláusula OUTPUT (Transact-SQL)

Ao capturar os resultados de uma cláusula OUTPUT em uma instrução INSERT, UPDATE, DELETE ou MERGE aninhada e inserir esses resultados em uma tabela ou exibição de destino, lembre-se do seguinte:

• Toda a operação é atômica. As instruções INSERT interna e DML aninhada que contêm a cláusula OUTPUT cláusula são executadas ou falham inteiramente.

• As seguintes restrições aplicam-se ao destino da instrução INSERT exterior:

  • O destino não pode ser uma tabela remota, exibição ou expressão de tabela comum.

  • O destino não pode ter uma restrição FOREIGN KEY nem ser referenciado por uma restrição FOREIGN KEY.

  • Não podem ser definidos gatilhos no destino.

  • O gatilho não pode participar de replicação de mesclagem ou de assinaturas atualizáveis para replicação transacional.

• As seguintes restrições aplicam-se à instrução DML aninhada:

  • O destino não pode ser uma tabela remota ou exibição particionada.

  • A própria origem não pode conter uma cláusula <dml_table_source>.

• A cláusula OUTPUT INTO não é suportada em instruções INSERT que contenham uma cláusula <dml_table_source>.

• @@ROWCOUNT retorna as linhas inseridas apenas pela instrução INSERT externa.

• @@IDENTITY, SCOPE_IDENTITY e IDENT_CURRENT retornam valores de identidade gerados apenas pela instrução DML aninhada, e não os valores gerados pela instrução INSERT externa.

• As notificações de consulta tratam a instrução como uma única entidade, e o tipo de qualquer mensagem criada será o tipo DML aninhado, mesmo que alteração significativa seja proveniente da própria instrução INSERT.

• Na cláusula <dml_table_source>, as cláusulas SELECT e WHERE não podem conter subconsultas, funções agregadas, funções de classificação, predicados de texto completo, funções definidas pelo usuário que executam acesso a dados nem a função TEXTPTR.

Colunas retornadas de OUTPUT refletem os dados da forma em que se encontram após a conclusão da instrução UPDATE, INSERT ou MERGE, mas antes da execução dos gatilhos.

No caso dos gatilhos INSTEAD OF, os resultados retornados são gerados como se INSERT, UPDATE ou DELETE tivesse ocorrido de fato, mesmo que nenhuma modificação aconteça como resultado da operação do gatilho. Se uma instrução que inclui uma cláusula OUTPUT for usada dentro do corpo de um disparador, devem ser usados aliases de tabela para fazer referência às tabelas inserted e deleted do disparador, para evitar referências duplicadas a colunas com as tabelas INSERTED e DELETED associadas à OUTPUT.

Se a cláusula OUTPUT for especificada sem especificação da palavra-chave INTO, o destino da operação de DML não poderá ter um gatilho habilitado definido para a ação DML fornecida. Por exemplo, se a cláusula OUTPUT estiver definida em uma instrução UPDATE, a tabela de destino não poderá ter nenhum gatilho UPDATE habilitado.

Se a opção sp_configuredisallow results from triggers estiver definida, uma cláusula OUTPUT sem cláusula INTO fará com que a instrução falhe quando ela for invocada a partir de um disparador.

A cláusula OUTPUT oferece suporte a tipos de dados de objeto grande: nvarchar(max), varchar(max), varbinary(max), text, ntext, image e xml. Quando a cláusula .WRITE é utilizada na instrução UPDATE para modificar uma coluna nvarchar(max), varchar(max) ou varbinary(max), as imagens completas dos valores de antes e depois são retornadas, caso sejam consultadas. A função TEXTPTR ( ) não pode aparecer como parte de uma expressão em uma coluna text, ntext ou image na cláusula OUTPUT.

Você pode usar OUTPUT em aplicativos que usam tabelas como filas ou para manter conjuntos de resultados intermediários. Ou seja, o aplicativo está somando ou removendo linhas constantemente da tabela. O exemplo a seguir usa a cláusula OUTPUT em uma instrução DELETE para retornar a linha excluída para o aplicativo de chamada.

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

Este exemplo remove uma linha de uma tabela usada como fila e retorna os valores excluídos para o aplicativo de processamento em uma única ação. Outras semânticas também podem ser implementadas, como usar uma tabela para implementar uma pilha. Porém, o SQL Server não garante a ordem em que as linhas são processadas e retornadas por instruções DML que usam a cláusula OUTPUT. Cabe ao aplicativo incluir uma cláusula WHERE apropriada que possa garantir a semântica desejada ou entender que, quando várias linhas puderem se qualificar para a operação DML, não haverá nenhuma garantia de ordem. O exemplo a seguir usa uma subconsulta e presume que exclusividade seja uma característica da coluna DatabaseLogID para implementar a semântica de ordenação desejada.

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;

--Results
--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

Observação
Use a dica de tabela READPAST nas instruções UPDATE e DELETE, se o cenário permitir que vários aplicativos executem uma leitura destrutiva de uma tabela. Isso impedirá que venham a acontecer problemas de bloqueios, caso outro aplicativo já esteja lendo o primeiro registro de qualificação na tabela.

O exemplo a seguir insere uma linha na tabela ScrapReason e usa a cláusula OUTPUT para retornar os resultados da instrução para a variável @MyTableVartable. Como a coluna ScrapReasonID está definida com uma propriedade IDENTITY, não é especificado um valor na instrução INSERT dessa coluna. Porém, note que o valor gerado pelo Mecanismo de Banco de Dados para a coluna é retornado na cláusula OUTPUT na coluna inserted.ScrapReasonID.

USE AdventureWorks2008R2;
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

O exemplo a seguir exclui todas as linhas da tabela ShoppingCartItem. A cláusula OUTPUT DELETED.* especifica que os resultados da instrução DELETE, que são todas as colunas nas linhas excluídas, sejam retornados para o aplicativo de chamada. A instrução SELECT que segue verifica os resultados da operação de exclusão na tabela ShoppingCartItem.

USE AdventureWorks2008R2;
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

O exemplo a seguir atualiza a coluna VacationHours da tabela Employee em 25% nas primeiras 10 linhas. Ele também define o valor da coluna ModifiedDate como a data atual. A cláusula OUTPUT retorna o valor VacationHours que existe antes da aplicação da instrução UPDATE na coluna deleted.VacationHours e o valor atualizado na coluna inserted.VacationHours para a variável @MyTableVartable.

A seguir estão duas instruções SELECT que retornam os valores em @MyTableVar e os resultados da operação de atualização na tabela Employee.

USE AdventureWorks2008R2;
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

O exemplo a seguir se baseia no exemplo C, definindo uma expressão na cláusula OUTPUT como diferença entre o valor VacationHours atualizado e o valor VacationHours antes de a atualização ser aplicada. O valor dessa expressão é retornado para a variável @MyTableVartable na coluna VacationHoursDifference.

USE AdventureWorks2008R2;
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

O exemplo a seguir atualiza a coluna ScrapReasonID da tabela WorkOrder em todas as ordens de trabalho com um ProductID e um ScrapReasonID especificados. A cláusula OUTPUT INTO retorna valores da tabela que está sendo atualizada (WorkOrder) e também da tabela Product. A tabela Product é usada na cláusula FROM para especificar as linhas a serem atualizadas. Como a tabela WorkOrder tem um gatilho AFTER UPDATE definido, é necessária a palavra-chave INTO.

USE AdventureWorks2008R2;
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

O exemplo a seguir exclui linhas da tabela ProductProductPhoto com base em critérios de pesquisa definidos na cláusula FROM da instrução DELETE. A cláusula OUTPUT retorna colunas da tabela que está sendo excluída (deleted.ProductID, deleted.ProductPhotoID) e colunas da tabela Product. Essa tabela é usada na cláusula FROM para especificar as linhas a serem excluídas.

USE AdventureWorks2008R2;
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

O exemplo a seguir atualiza um valor parcial em DocumentSummary e uma coluna nvarchar(max) na tabela Production.Document usando a cláusula .WRITE. A palavra components é substituída pela palavra features especificando-se a palavra de substituição, o local de início (deslocamento) da palavra a ser substituída nos dados existentes e o número de caracteres a serem substituídos (comprimento). O exemplo usa a cláusula OUTPUT para retornar as imagens da coluna DocumentSummary de antes e depois para a variável @MyTableVartable. Note que são retornadas as imagens completas de antes e depois da coluna DocumentSummary.

USE AdventureWorks2008R2;
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

O exemplo a seguir usa a cláusula OUTPUT em um gatilho para retornar os resultados da operação do gatilho. Primeiro, uma exibição é criada na tabela ScrapReason e, em seguida, um gatilho INSTEAD OF INSERT é definido na exibição, permitindo que apenas a coluna Name da tabela base seja modificada pelo usuário. Como a coluna ScrapReasonID é uma coluna IDENTITY na tabela base, o gatilho ignora o valor fornecido pelo usuário. Isso permite ao Mecanismo de Banco de Dados gerar o valor correto automaticamente. O valor fornecido pelo usuário para ModifiedDate também é ignorado, sendo definido como a data atual. A cláusula OUTPUT retorna os valores inseridos de fato na tabela ScrapReason.

USE AdventureWorks2008R2;
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

Eis o conjunto de resultados gerado no dia 12 de abril de 2004 ('2004-04-12'). Note que as colunas ScrapReasonIDActual e ModifiedDate refletem os valores gerados pela operação do gatilho, em vez dos valores fornecidos na instrução INSERT.

ScrapReasonID  Name             ModifiedDate

-------------  ---------------- -----------------------

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

O exemplo a seguir cria a tabela EmployeeSales e, em seguida, insere várias linhas nela por meio de uma instrução INSERT com uma instrução SELECT, para recuperar dados das tabelas de origem. A tabela EmployeeSales contém uma coluna de identidade (EmployeeID) e uma coluna computada (ProjectedSales). Como esses valores são gerados pelo Mecanismo de banco de dados do SQL Server durante a operação de inserção, nenhuma dessas colunas pode ser definida em @MyTableVar.

USE AdventureWorks2008R2 ;
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(
  LastName     nvarchar(20) NOT NULL,
  FirstName    nvarchar(20) NOT NULL,
  CurrentSales money NOT NULL
  );

INSERT INTO dbo.EmployeeSales (LastName, FirstName, CurrentSales)
  OUTPUT INSERTED.LastName, 
         INSERTED.FirstName, 
         INSERTED.CurrentSales
  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 LastName, FirstName, CurrentSales
FROM @MyTableVar;
GO
SELECT EmployeeID, LastName, FirstName, CurrentSales, ProjectedSales
FROM dbo.EmployeeSales;
GO

O exemplo a seguir exclui linhas da tabela ProductProductPhoto com base em critérios de pesquisa definidos na cláusula FROM da instrução DELETE. A cláusula OUTPUT INTO retorna colunas da tabela que está sendo excluída (deleted.ProductID, deleted.ProductPhotoID) e colunas da tabela Product para a variável @MyTableVartable. A tabela Product é usada na cláusula FROM para especificar as linhas a serem excluídas. A cláusula OUTPUT retorna as colunas deleted.ProductID e deleted.ProductPhotoID e a data e a hora em que a linha foi excluída de ProductProductPhoto para o aplicativo de chamada.

USE AdventureWorks2008R2;
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
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

O exemplo a seguir captura dados retornados da cláusula OUTPUT de uma instrução MERGE e insere esses dados em outra tabela. A instrução MERGE atualiza a coluna Quantity da tabela ProductInventory diariamente, com base nos pedidos processados na tabela SalesOrderDetail. Ela também exclui linhas de produtos cujos inventários caem para 0 ou menos. O exemplo captura as linhas excluídas e as insere em outra tabela, ZeroInventory, que rastreia produtos sem-estoque.

USE AdventureWorks2008R2;
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
           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;