Cláusula OUTPUT (Transact-SQL)

  • Artículo

Se aplica a: SQL Server Azure SQL Database Azure SQL Managed Instance

Devuelve información de , o expresiones basadas en , cada fila afectada por una INSERTinstrucción , UPDATE, DELETEo MERGE . Estos resultados se pueden devolver a la aplicación de procesamiento para que los utilice en mensajes de confirmación, archivado y otros requisitos similares de una aplicación. Los resultados también se pueden insertar en una tabla o variable de tabla. Además, puede capturar los resultados de una OUTPUT cláusula en una instrucción anidada INSERT, UPDATE, DELETE, o e insertar esos resultados en una tabla o MERGE vista de destino.

Nota:

Una UPDATEinstrucción , INSERTo DELETE que tiene una OUTPUT cláusula devolverá filas al cliente incluso si la instrucción encuentra errores y se revierte. No se debe usar el resultado si se produce algún error al ejecutar la instrucción.

Se usa en:

Convenciones de sintaxis de Transact-SQL

Sintaxis

<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

Argumentos

@table_variable

Especifica una variable tabla en la que se insertan las filas devueltas en lugar de devolverse al autor de la llamada. @table_variable debe declararse antes de la INSERTinstrucción , UPDATE, DELETEo MERGE .

Si no se especifica column_list, la variable table debe tener el mismo número de columnas que el conjunto de resultados de OUTPUT. Las excepciones son las columnas de identidad y calculadas, que deben omitirse. Si se especifica lista_de_columnas, las columnas omitidas deben aceptar valores NULL o tener valores predeterminados asignados.

Para obtener más información sobre las variables de tabla , consulte tabla.

tabla_de_salida

Especifica una tabla en la que se insertan las filas devueltas en lugar de devolverse al autor de la llamada. output_table podría ser una tabla temporal.

Si no se especifica column_list, la tabla debe tener el mismo número de columnas que el conjunto de resultados de OUTPUT. Las excepciones son las columnas de identidad y calculadas, que deben omitirse. Si se especifica lista_de_columnas, las columnas omitidas deben aceptar valores NULL o tener valores predeterminados asignados.

output_table no puede:

  • Tener definidos desencadenadores habilitados.
  • Participar en cualquiera de los lados de una FOREIGN KEY restricción.
  • Tener CHECK restricciones o reglas habilitadas.

lista_de_columnas

Lista opcional de nombres de columna en la tabla de destino de la INTO cláusula . Es análogo a la lista de columnas permitida en la instrucción INSERT .

scalar_expression

Cualquier combinación de símbolos y operadores que se evalúe como un solo valor. No se permiten funciones de agregado en scalar_expression.

Cualquier referencia a columnas de la tabla que se va a modificar debe calificar con el INSERTED prefijo o DELETED .

column_alias_identifier

Nombre alternativo que se utiliza para hacer referencia al nombre de la columna.

DELETED

Prefijo de columna que especifica el valor eliminado por la operación de actualización o eliminación, y los valores existentes que no cambian con la operación actual. Las columnas con DELETED prefijo reflejan el valor antes de que se complete la UPDATEinstrucción , DELETEo MERGE .

DELETED no se puede usar con la OUTPUT cláusula en la INSERT instrucción .

INSERTED

Prefijo de columna que especifica el valor agregado por la operación de inserción o actualización y los valores existentes que no cambian con la operación actual. Las columnas prefijos con INSERTED reflejan el valor después de que se complete la UPDATEinstrucción , INSERTo MERGE , pero antes de que se ejecuten los desencadenadores.

INSERTED no se puede usar con la OUTPUT cláusula en la DELETE instrucción .

from_table_name

Prefijo de columna que especifica una tabla incluida en la FROM cláusula de una DELETEinstrucción , UPDATEo MERGE que se usa para especificar las filas que se van a actualizar o eliminar.

Si la tabla que se va a modificar también se especifica en la FROM cláusula , cualquier referencia a columnas de esa tabla debe calificarse con el INSERTED prefijo o DELETED .

*

El asterisco (*) especifica que todas las columnas afectadas por la acción eliminar, insertar o actualizar se devuelven en el orden en que existen en la tabla.

Por ejemplo, en la instrucción siguiente DELETE se OUTPUT DELETED.* devuelven todas las columnas eliminadas de la ShoppingCartItem tabla:

DELETE Sales.ShoppingCartItem
    OUTPUT DELETED.*;

column_name

Referencia de columna explícita. Cualquier referencia a la tabla que se va a modificar debe calificar correctamente mediante el INSERTED prefijo o DELETED según corresponda, por ejemplo: INSERTED.<column_name>.

$action

Solo está disponible para la MERGE instrucción . Especifica una columna de tipo nvarchar(10) en la OUTPUT cláusula de una MERGE instrucción que devuelve uno de los tres valores de cada fila: INSERT, UPDATEo DELETE, según la acción que se realizó en esa fila.

Comentarios

La OUTPUT <dml_select_list> cláusula y la OUTPUT <dml_select_list> INTO { @table_variable | output_table } cláusula se pueden definir en una sola INSERTinstrucción , UPDATE, DELETEo MERGE .

Nota:

A menos que se indique lo contrario, las referencias a la cláusula OUTPUT se aplican tanto a la cláusula OUTPUT como a la cláusula OUTPUT INTO.

La OUTPUT cláusula puede ser útil para recuperar el valor de identidad o columnas calculadas después de una INSERT operación o UPDATE .

Cuando se incluye una columna calculada en <dml_select_list>, la columna correspondiente de la tabla de salida o la variable de tabla no es una columna calculada. Los valores de la nueva columna son los que se calcularon en el momento en que se ejecutó la instrucción.

El orden en el que se aplican los cambios a la tabla y el orden en el que se insertan las filas en la tabla de salida o la variable de tabla, no se garantiza que se correspondan.

Si los parámetros o variables se modifican como parte de una UPDATE instrucción, la OUTPUT cláusula siempre devuelve el valor del parámetro o variable tal como estaba antes de que se ejecutara la instrucción en lugar del valor modificado.

Puede usar OUTPUT con una UPDATE instrucción o DELETE colocada en un cursor que use WHERE CURRENT OF sintaxis.

La cláusula OUTPUT no se admite en las siguientes instrucciones:

  • Instrucciones DML que hacen referencia a vistas locales con particiones, vistas distribuidas con particiones o tablas remotas.

  • INSERT instrucciones que contienen una EXECUTE instrucción .

  • Los predicados de texto completo no están permitidos en la cláusula OUTPUT cuando el nivel de compatibilidad de la base de datos está establecido en 100.

  • La cláusula OUTPUT INTO no se puede utilizar para realizar inserciones en vistas o en una función de conjunto de filas.

  • No se puede crear una función definida por el usuario si contiene una OUTPUT INTO cláusula que tiene una tabla como destino.

Para evitar el comportamiento no determinista, la cláusula OUTPUT no puede contener las referencias siguientes:

  • Subconsultas o funciones definidas por el usuario que obtienen acceso a datos de usuario o del sistema, o que se asume que obtienen dicho acceso. Se supone que las funciones definidas por el usuario realizan el acceso a los datos si no están enlazadas a un esquema.

  • Una columna de una vista o función insertada con valores de tabla si la columna se define mediante uno de los métodos siguientes:

    • Una subconsulta.

    • Una función definida por el usuario que obtiene acceso a datos de usuario o del sistema, o que se asume que obtiene dicho acceso.

    • Una columna calculada que contiene una función definida por el usuario que obtiene acceso a datos de usuario o del sistema en su definición.

    Cuando SQL Server detecta este tipo de columna en la cláusula OUTPUT, se produce el error 4186.

Inserción de los datos devueltos por una cláusula OUTPUT en una tabla

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

  • Toda la operación es atómica. Tanto la INSERT instrucción como la instrucción DML anidada que contiene la OUTPUT cláusula execute o se produce un error en toda la instrucción.

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

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

    • El destino no puede tener una FOREIGN KEY restricción o hacer referencia a esta por una FOREIGN KEY restricción.

    • No se pueden definir desencadenadores en el destino.

    • El destino no puede participar en la replicación de combinación 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 OUTPUT INTO cláusula no se admite en INSERT instrucciones que contienen una <dml_table_source> cláusula .

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

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

  • Las notificaciones de consulta tratan la instrucción como una sola entidad y el tipo de cualquier mensaje que se crea es el tipo del DML anidado, incluso si el cambio significativo procede de la propia instrucción externa INSERT .

  • En la <dml_table_source> cláusula , las SELECT cláusulas y WHERE no pueden incluir subconsultas, funciones de agregado, funciones de clasificación, predicados de texto completo, funciones definidas por el usuario que realizan acceso a datos o la TEXTPTR() función .

Paralelismo

Una OUTPUT cláusula que devuelve resultados al cliente, o variable de tabla, siempre usa un plan serie.

En el contexto de una base de datos establecida en el nivel de compatibilidad 130 o superior, si una INSERT...SELECT operación usa una WITH (TABLOCK) sugerencia para la SELECT instrucción y también usa OUTPUT...INTO para insertar en una tabla temporal o de usuario, la tabla de destino de INSERT...SELECT es apta para paralelismo en función del costo del subárbol. La tabla de destino a la que se hace referencia en la OUTPUT INTO cláusula no es apta para paralelismo.

Desencadenadores

Las columnas devueltas de OUTPUT reflejan los datos a medida que se completan las INSERTinstrucciones , UPDATEo DELETE , pero antes de que se ejecuten los desencadenadores.

En INSTEAD OF el caso de los desencadenadores, los resultados devueltos se generan como si se INSERThubiera producido realmente , UPDATEo DELETE incluso si no se han producido modificaciones como resultado de la operación del desencadenador. Si se usa una instrucción que incluye una OUTPUT cláusula dentro del cuerpo de un desencadenador, se deben usar alias de tabla para hacer referencia a las tablas insertadas y eliminadas del desencadenador para evitar duplicar las referencias de columna con las INSERTED tablas y DELETED asociadas a OUTPUT.

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

Si se establece la sp_configure opción no permitir resultados de desencadenadores, una OUTPUT cláusula sin una INTO cláusula hace que se produzca un error en la instrucción cuando se invoca desde dentro de un desencadenador.

Tipos de datos

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 usa la .WRITE cláusula en la UPDATE instrucción para modificar una columna nvarchar(max), varchar(max)o varbinary(max), se devuelven las imágenes completas antes y después de los valores si se hace referencia a ellos. 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.

Colas

Se puede utilizar OUTPUT 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 usa la OUTPUT cláusula en una DELETE instrucción para devolver la fila eliminada a la aplicación que realiza la llamada.

USE AdventureWorks2022;
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 pueden implementar otras semánticas, como el uso de una tabla para implementar una pila. No obstante, SQL Server no garantiza el orden en el que las instrucciones DML procesan y devuelven las filas por medio de la cláusula OUTPUT. Es necesario que la aplicación incluya una cláusula adecuada WHERE que pueda garantizar la semántica deseada o comprender que, cuando varias filas pueden calificar para la operación DML, no hay ningún orden garantizado. 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 *
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;

He aquí los resultados:

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

Nota:

Use la READPAST sugerencia de tabla en UPDATE las instrucciones y DELETE si el escenario permite que varias aplicaciones realicen una lectura destructiva de una tabla. De esta forma se impide que surjan problemas de bloqueo si otra aplicación ya está leyendo el primer registro de la tabla que reúne los requisitos.

Permisos

SELECT Los permisos son necesarios en las columnas recuperadas mediante <dml_select_list> o usadas en <scalar_expression>.

INSERT Los permisos son necesarios en todas las tablas especificadas en <output_table>.

Ejemplos

Los ejemplos de código de Transact-SQL de este artículo utilizan la base de datos de ejemplo AdventureWorks2022 o AdventureWorksDW2022, que se pueden descargar desde la página principal de Ejemplos y proyectos de la comunidad de Microsoft SQL Server.

A Uso de OUTPUT INTO con una instrucción INSERT

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 de la tabla @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, el valor generado por el Motor de base de datos para esa columna se devuelve en la cláusula OUTPUT de la columna 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. Uso de OUTPUT con una instrucción DELETE

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 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. Uso de OUTPUT INTO con una instrucción UPDATE

En el ejemplo siguiente se actualiza un 25 por ciento la columna VacationHours de las 10 primeras filas de la tabla Employee. 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 en la columna INSERTED.VacationHours en la variable de tabla @MyTableVar.

Las dos instrucciones SELECT que le siguen devuelven los valores en @MyTableVar y los resultados de la operación de actualización en la tabla 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. Uso de OUTPUT INTO para devolver una expresión

El ejemplo siguiente, que se basa en el ejemplo C, 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 aplicar la actualización. El valor de esta expresión se devuelve a la variable de la tabla @MyTableVar en la columna 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

E. Uso de OUTPUT INTO con from_table_name en una instrucción UPDATE

En el ejemplo siguiente se actualiza la columna ScrapReasonID de la tabla WorkOrder para todas las órdenes de trabajo en las que se especifique 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 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. Uso de OUTPUT INTO con from_table_name en una instrucción DELETE

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 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. Uso de OUTPUT INTO con un tipo de datos de objetos grandes

En el ejemplo siguiente se actualiza un valor parcial en DocumentSummary, una columna nvarchar(max) de la Production.Document tabla mediante la .WRITE cláusula . 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 usa la cláusula OUTPUT para devolver las imágenes anteriores y posteriores de la columna DocumentSummary a la variable de la tabla @MyTableVar. Se devuelven las imágenes anterior y posterior completas de la columna 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. Uso de OUTPUT en un desencadenador INSTEAD OF

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

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

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

I. Uso de OUTPUT INTO con columnas de identidad y calculadas

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).

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. Uso de OUTPUT y OUTPUT INTO en una sola instrucción

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 y DELETED.ProductPhotoID, y columnas de la tabla Product a la variable @MyTableVar. La tabla Product se utiliza en la cláusula FROM para especificar las filas que se van a eliminar. La OUTPUT cláusula devuelve las DELETED.ProductIDcolumnas , DELETED.ProductPhotoID y la fecha y hora en que se eliminó la fila de la ProductProductPhoto tabla a la aplicación que realiza la llamada.

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. Inserción de los datos devueltos por una cláusula OUTPUT

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

Contenido relacionado