OUTPUT (cláusula de Transact-SQL)

Al capturar los resultados de una cláusula OUTPUT en una instrucción INSERT, UPDATE, DELETE o MERGE anidada e insertarlos en una tabla de destino, tenga presente la información siguiente:

• Toda la operación es atómica. Se ejecutarán la instrucción INSERT y la instrucción DML anidada que contiene la cláusula OUTPUT, o bien se producirá un error en toda la instrucción.

• Las restricciones siguientes se aplican al destino de la instrucción INSERT externa:

  • El destino no puede ser una expresión de tabla común, vista o tabla remota.

  • El destino no puede tener una restricción FOREIGN KEY, ni ser objeto de referencia por una restricción FOREIGN KEY.

  • No se pueden definir desencadenadores en el destino.

  • El destino no puede participar en la replicación de mezcla ni en las suscripciones actualizables para la replicación transaccional.

• Las restricciones siguientes se aplican a la instrucción DML anidada:

  • El destino no puede ser una tabla remota ni una vista con particiones.

  • El propio origen no puede contener una cláusula <dml_table_source>.

• La cláusula OUTPUT INTO no se admite en instrucciones INSERT que contengan una cláusula <dml_table_source>.

• @@ROWCOUNT devuelve las filas insertadas únicamente por la instrucción INSERT externa.

• @@IDENTITY, SCOPE_IDENTITY e IDENT_CURRENT devuelven los valores de identidad generados solamente por la instrucción DML anidada, y no los generados por la instrucción INSERT externa.

• Las notificaciones de consulta tratan la instrucción como una entidad única, y el tipo de cualquier mensaje creado es el del DML anidado, aunque el cambio significativo provenga de la propia instrucción INSERT externa.

• En la cláusula <dml_table_source>, las cláusulas SELECT y WHERE no pueden incluir subconsultas, funciones de agregado, funciones de categoría, predicados de texto completo, funciones definidas por el usuario que realicen accesos a datos, ni la función TEXTPTR.

Las columnas devueltas de OUTPUT reflejan los datos tal como estaban después de completarse la instrucción INSERT, UPDATE o DELETE, pero antes de ejecutarse los desencadenadores.

En el caso de los desencadenadores INSTEAD OF, los resultados devueltos se generan como si la operación de INSERT, UPDATE o DELETE se hubiese producido realmente, aunque no se produzcan modificaciones como resultado de la operación del desencadenador. Si se utiliza una instrucción que incluye una cláusula OUTPUT en el cuerpo de un desencadenador, deben utilizarse alias de tabla para hacer referencia a las tablas inserted y deleted del desencadenador con el fin de evitar la duplicación de las referencias a columnas con las tablas INSERTED y DELETED asociadas a OUTPUT.

Si la cláusula OUTPUT se especifica sin especificar también la palabra clave INTO, el destino de la operación DML no puede tener definido ningún desencadenador habilitado para la acción DML dada. Por ejemplo, si se define la cláusula OUTPUT en una instrucción UPDATE, la tabla de destino no puede tener desencadenadores UPDATE habilitados.

Si se establece la opción disallow results from triggers de sp_configure, una cláusula OUTPUT sin una cláusula INTO hará que la instrucción genere un error cuando se invoque desde un desencadenador.

La cláusula OUTPUT admite los tipos de datos de objetos grandes: nvarchar(max), varchar(max), varbinary(max), text, ntext, image y xml. Cuando se utiliza la cláusula .WRITE en la instrucción UPDATE para modificar una columna de tipo nvarchar(max), varchar(max) o varbinary(max), se devuelven las imágenes anterior y posterior completas de los valores si se hace referencia a ellas. La función TEXTPTR( ) no puede aparecer como parte de una expresión en una columna de tipo text, ntext o image en la cláusula OUTPUT.

OUTPUT se puede utilizar en aplicaciones que utilizan tablas como colas, o para contener conjuntos de resultados intermedios. Dicho de otro modo, la aplicación agrega o quita filas de la tabla constantemente. En el ejemplo siguiente se utiliza la cláusula OUTPUT en una instrucción DELETE para devolver la fila eliminada a la aplicación que realiza la llamada.

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

En este ejemplo, se quita una fila de una tabla utilizada como cola y se devuelven los valores eliminados a la aplicación de procesamiento en una única acción. También se puede implementar otro tipo de semántica, como utilizar una tabla para implementar una pila. No obstante, SQL Server no garantiza el orden en que las instrucciones DML procesan y devuelven las filas por medio de la cláusula OUTPUT. Es la aplicación la que debe incluir una cláusula WHERE que garantice la semántica deseada, o reconocer que, si hay varias filas aptas para la operación DML, no se garantiza el orden. En el ejemplo siguiente se utiliza una subconsulta y se supone que la unicidad es una característica de la columna DatabaseLogID para implementar la semántica de ordenación deseada.

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 id, employee FROM dbo.table1;

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

PRINT 'table1, after delete';
SELECT id, employee FROM table1;

PRINT '@MyTableVar, after delete';
SELECT id, employee FROM @MyTableVar;

DROP TABLE dbo.table1;

--Results
--table1, before delete
--id          employee
------------- ------------------------------
--1           Fred
--2           Tom
--3           Sally
--4           Alice
--
--table1 results, after delete
--id          employee
------------- ------------------------------
--1           Fred
--3           Sally
--
--@MyTableVar results, after delete
--id          employee
------------- ------------------------------
--2           Tom
--4           Alice

En el siguiente ejemplo se inserta una fila en la tabla ScrapReason y se utiliza la cláusula OUTPUT para devolver los resultados de la instrucción a la variable table@MyTableVar. Como la columna ScrapReasonID se ha definido con una propiedad IDENTITY, no se especifica ningún valor en la instrucción INSERT de esa columna. No obstante, tenga en cuenta que el valor generado por el Database Engine (Motor de base de datos) para esa columna se devuelve en la cláusula OUTPUT de la columna INSERTED.ScrapReasonID.

USE AdventureWorks;
GO
DECLARE @MyTableVar table( ScrapReasonID 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 ScrapReasonID, Name, ModifiedDate FROM @MyTableVar;
--Display the result set of the table.
SELECT ScrapReasonID, Name, ModifiedDate 
FROM Production.ScrapReason;
GO

En el ejemplo siguiente se eliminan todas las filas de la tabla ShoppingCartItem. La cláusula OUTPUT DELETED.* especifica que se devuelvan a la aplicación que realiza la llamada los resultados de la instrucción DELETE, es decir, todas las columnas de las filas eliminadas. La instrucción SELECT posterior comprueba los resultados de la operación de eliminación en la tabla ShoppingCartItem.

USE AdventureWorks;
GO
DELETE Sales.ShoppingCartItem
OUTPUT DELETED.* 
WHERE ShoppingCartID = 20621;

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

En el ejemplo siguiente la columna VacationHours de la tabla Employee se actualiza un 25 por ciento en las 10 primeras filas y también se establece en la fecha actual el valor de la columna ModifiedDate. La cláusula OUTPUT devuelve el valor de VacationHours antes de aplicar la instrucción UPDATE en la columna deleted.VacationHours y el valor actualizado de la columna inserted.VacationHours en la variable de table@MyTableVar.

A continuación, dos instrucciones SELECT devuelven los valores de @MyTableVar y los resultados de la operación de actualización en la tabla Employee.

USE AdventureWorks;
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.EmployeeID,
       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) EmployeeID, VacationHours, ModifiedDate
FROM HumanResources.Employee;
GO

En el ejemplo siguiente, que se basa en el ejemplo C, se define una expresión en la cláusula OUTPUT como la diferencia entre el valor actualizado de VacationHours y el valor de VacationHours antes de aplicarse la actualización. El valor de esta expresión se devuelve a la variable table@MyTableVar en la columna VacationHoursDifference.

USE AdventureWorks;
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.EmployeeID,
       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) EmployeeID, VacationHours, ModifiedDate
FROM HumanResources.Employee;
GO

En el ejemplo siguiente se actualiza la columna ScrapReasonID de la tabla WorkOrder para todas las órdenes de trabajo que tengan especificados ProductID y ScrapReasonID. La cláusula OUTPUT INTO devuelve los valores de la tabla que se actualiza (WorkOrder) y de la tabla Product. La tabla Product se utiliza en la cláusula FROM para especificar las filas que se van a actualizar. Dado que la tabla WorkOrder tiene definido un desencadenador AFTER UPDATE, se requiere la palabra clave INTO.

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

En el ejemplo siguiente se eliminan las filas de la tabla ProductProductPhoto según los criterios de búsqueda definidos en la cláusula FROM de la instrucción DELETE. La cláusula OUTPUT devuelve columnas de la tabla que se elimina (DELETED.ProductID, DELETED.ProductPhotoID) y de la tabla Product. La tabla se utiliza en la cláusula FROM para especificar las filas que se van a eliminar.

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

En el ejemplo siguiente se actualiza un valor parcial de DocumentSummary, una columna de tipo nvarchar(max) de la tabla Production.Document, utilizando la cláusula .WRITE. La palabra components se sustituye por la palabra features al especificar la palabra sustituta, la ubicación inicial (desplazamiento) de la palabra que se va a sustituir en los datos existentes y el número de caracteres que se va a sustituir (longitud). En el ejemplo se utiliza la cláusula OUTPUT para devolver las imágenes anterior y posterior de la columna DocumentSummary en la variable table@MyTableVar. Observe que se devuelven las imágenes anterior y posterior completas de la columna DocumentSummary.

USE AdventureWorks;
GO
DECLARE @MyTableVar table (
    DocumentID int NOT NULL,
    SummaryBefore nvarchar(max),
    SummaryAfter nvarchar(max));
UPDATE Production.Document
SET DocumentSummary .WRITE (N'features',28,10)
OUTPUT inserted.DocumentID,
       deleted.DocumentSummary, 
       inserted.DocumentSummary 
    INTO @MyTableVar
WHERE DocumentID = 3 ;
SELECT DocumentID, SummaryBefore, SummaryAfter 
FROM @MyTableVar;
GO

En el ejemplo siguiente se utiliza la cláusula OUTPUT en un desencadenador para devolver los resultados de la operación del desencadenador. En primer lugar se crea una vista en la tabla ScrapReason y, después, en la vista se define un desencadenador INSTEAD OF INSERT que permite al usuario modificar únicamente la columna Name de la tabla base. Puesto que la columna ScrapReasonID es una columna IDENTITY de la tabla base, el desencadenador omite el valor suministrado por el usuario. Esto permite que el Database Engine (Motor de base de datos) genere automáticamente el valor correcto. Asimismo, se omite el valor suministrado por el usuario para ModifiedDate, que se establece en la fecha actual. La cláusula OUTPUT devuelve los valores reales insertados en la tabla ScrapReason.

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

Éste es el conjunto de resultados generado el 12 de abril de 2004 ('2004-04-12'). Tenga en cuenta que las columnas ScrapReasonIDActual y ModifiedDate reflejan los valores generados en la operación del desencadenador en lugar de los valores proporcionados en la instrucción INSERT.

ScrapReasonID  Name             ModifiedDate

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

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

En el ejemplo siguiente se crea la tabla EmployeeSales y, después, se insertan en ella varias filas utilizando una instrucción INSERT con una instrucción SELECT para recuperar los datos de las tablas de origen. La tabla EmployeeSales contiene una columna de identidad (EmployeeID) y una columna calculada (ProjectedSales). Puesto que SQL Server Database Engine (Motor de base de datos de SQL Server) genera estos valores durante la operación de inserción, ninguna de estas columnas se puede definir en @MyTableVar.

USE AdventureWorks ;
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 HumanResources.Employee AS e
        INNER JOIN Sales.SalesPerson AS sp
        ON e.EmployeeID = sp.SalesPersonID 
        INNER JOIN Person.Contact AS c
        ON e.ContactID = c.ContactID
    WHERE e.EmployeeID 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

En el ejemplo siguiente se eliminan las filas de la tabla ProductProductPhoto según los criterios de búsqueda definidos en la cláusula FROM de la instrucción DELETE. La cláusula OUTPUT INTO devuelve las columnas de la tabla que se elimina (deleted.ProductID, deleted.ProductPhotoID) y columnas de la tabla Product a la variable table@MyTableVar. La tabla Product se utiliza en la cláusula FROM para especificar las filas que se van a eliminar. La cláusula OUTPUT devuelve las columnas deleted.ProductID y deleted.ProductPhotoID, y la fecha y hora de eliminación de la fila de la tabla ProductProductPhoto a la aplicación que realiza la llamada.

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

El ejemplo siguiente captura datos devueltos por la cláusula OUTPUT de una instrucción MERGE y los inserta en otra tabla. La instrucción MERGE actualiza diariamente la columna Quantity de la tabla ProductInventory en función de los pedidos procesados en la tabla SalesOrderDetail. También elimina las filas correspondientes a los productos cuyas existencias están en el valor 0 o por debajo de este valor. En el ejemplo, se capturan las filas que se eliminan y se insertan en otra tabla, ZeroInventory, que realiza el seguimiento de los productos sin existencias.

USE AdventureWorks;
GO
IF OBJECT_ID(N'Production.ZeroInventory', N'U') IS NOT NULL
    DROP TABLE Production.ZeroInventory;
GO
--Create ZeroInventory table.
CREATE TABLE Production.ZeroInventory (ProductID int);
GO

INSERT INTO Production.ZeroInventory (ProductID)
SELECT ProductID
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 = '20030401'
           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';
GO
SELECT ProductID FROM Production.ZeroInventory;