Clause OUTPUT (Transact-SQL)

Lorsque vous capturez les résultats d'une clause OUTPUT dans une instruction INSERT, UPDATE, DELETE ou MERGE imbriquée et insérez ces résultats dans une table cible, n'oubliez pas les informations suivantes :

• L'opération entière est atomique. Soit l'instruction INSERT et l'instruction DML imbriquée qui contient la clause OUTPUT s'exécutent toutes les deux, soit l'instruction entière échoue.

• Les restrictions suivantes s'appliquent à la cible de l'instruction INSERT externe :

  • La cible ne peut pas être une expression de table commune, une vue ou une table distante.

  • La cible ne peut pas avoir de contrainte FOREIGN KEY ou être référencée par une contrainte FOREIGN KEY.

  • Des déclencheurs ne peuvent pas être définis sur la cible.

  • La cible ne peut pas participer à la réplication de fusion ou à des abonnements pouvant être mis à jour pour la réplication transactionnelle.

• Les restrictions suivantes s'appliquent à l'instruction DML imbriquée :

  • La cible ne peut pas être une table distante ou une vue partitionnée.

  • La source elle-même ne peut pas contenir de clause <dml_table_source>.

• La clause OUTPUT INTO n'est pas prise en charge dans les instructions INSERT qui contiennent une clause <dml_table_source>.

• @@ROWCOUNT retourne les lignes insérées uniquement par l'instruction INSERT externe.

• @@IDENTITY, SCOPE_IDENTITY et IDENT_CURRENT retournent les valeurs d'identité générées uniquement par l'instruction DML imbriquée, et non celles générées par l'instruction INSERT externe.

• Les notifications de requête traitent l'instruction comme une entité unique, et le type de tout message créé sera le type de l'instruction DML imbriquée, même si la modification significative provient de l'instruction INSERT externe elle-même.

• Dans la clause <dml_table_source>, les clauses SELECT et WHERE ne peuvent pas inclure de sous-requêtes, de fonctions d'agrégation, de fonctions de classement, de prédicats de texte intégral, de fonctions définies par l'utilisateur qui effectuent l'accès aux données ou la fonction TEXTPTR.

Les colonnes retournées depuis l'instruction OUTPUT illustrent les données, telles qu'elles sont après la fin de l'instruction INSERT, UPDATE ou DELETE mais avant l'exécution des déclencheurs.

Pour les déclencheurs INSTEAD OF, les résultats retournés sont produits, comme si l'instruction INSERT, UPDATE ou DELETE avait eu lieu, même en cas d'absence de modifications suite à l'opération des déclencheurs. Si une instruction comprenant une clause OUTPUT est utilisée à l'intérieur du corps d'un déclencheur, les alias de table doivent être utilisés pour faire référence aux tables inserted et deleted du déclencheur, afin d'éviter des références de colonne en double avec les tables INSERTED et DELETED associées à OUTPUT.

Si la clause OUTPUT est spécifiée sans mot clé INTO, la cible de l'opération DML ne peut pas comporter de déclencheur activé pour l'action DML en question. Par exemple, si la clause OUTPUT est définie dans une instruction UPDATE, la table cible ne peut pas comporter de déclencheurs UPDATE activés.

Si l'option disallow results from triggerssp_configure est définie, une clause OUTPUT sans clause INTO provoque l'échec de l'instruction lors de son appel à partir d'un déclencheur.

La clause OUTPUT prend en charge les types de données d'objets volumineux : nvarchar(max), varchar(max), varbinary(max), text, ntext, image et xml. Si vous utilisez la clause .WRITE dans l'instruction UPDATE pour modifier une colonne nvarchar(max), varchar(max) ou varbinary(max), les images complètes avant et après des valeurs sont retournées si elles sont référencées. La fonction TEXTPTR( ) ne peut pas s'afficher comme partie intégrante d'une expression sur une colonne text, ntext ou image de la clause OUTPUT.

Vous pouvez utiliser OUTPUT dans les applications utilisant les tables comme files d'attente ou garder les ensembles de résultats intermédiaires. En d'autres termes, l'application ajoute ou supprime constamment des lignes de la table. L'exemple suivant utilise la clause OUTPUT dans une instruction DELETE afin de retourner la ligne supprimée à l'application appelante.

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

Cet exemple supprime une ligne d'une table utilisée comme file d'attente et retourne les valeurs supprimées à l'application de traitement en une seule fois. D'autres sémantiques peuvent également être mises en place, notamment l'utilisation d'une table pour implémenter une pile. Cependant, SQL Server ne garantit pas l'ordre de traitement et de retour des lignes par les instructions DML à l'aide de la clause OUTPUT. C'est à l'application d'inclure une clause WHERE adéquate pour obtenir les sémantiques souhaitées ou de comprendre qu'aucun ordre n'est garanti lorsque plusieurs lignes sont qualifiées pour l'opération DML. L'exemple suivant utilise une sous-requête et suppose que le caractère unique est une caractéristique de la colonne DatabaseLogID, afin de mettre en place les sémantiques de classement souhaitées.

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

L'exemple suivant insère une ligne dans la table ScrapReason et utilise la clause OUTPUT pour retourner les résultats de l'instruction à la variable @MyTableVartable. Étant donné que la colonne ScrapReasonID est définie avec une propriété IDENTITY, aucune valeur n'est spécifiée dans l'instruction INSERT pour cette colonne. Cependant, notez que la valeur générée par le moteur de base de données pour cette colonne est retournée dans la clause OUTPUT de la colonne 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

L'exemple suivant supprime toutes les lignes de la table ShoppingCartItem. La clause OUTPUT DELETED.* spécifie que les résultats de l'instruction DELETE, à savoir toutes les colonnes dans les lignes supprimées, sont retournés vers l'application appelante. L'instruction SELECT suivante vérifie les résultats de l'opération de suppression dans la table 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

L'exemple suivant met à jour la colonne VacationHours à hauteur de 25 % pour 10 premières lignes aléatoires dans la table Employee. Il définit également la valeur de la colonne ModifiedDate sur la date actuelle. La clause OUTPUT retourne la valeur de VacationHours qui existe avant l'application de l'instruction UPDATE dans la colonne deleted.VacationHours et la valeur mise à jour dans la colonne inserted.VacationHours à la variable @MyTableVartable.

Deux instructions SELECT suivent ; elles retournent les valeurs dans @MyTableVar et les résultats de la mise à jour dans la table 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

L'exemple suivant reprend l'exemple C en définissant une expression dans la clause OUTPUT comme étant la différence entre la valeur mise à jour VacationHours et la valeur VacationHours avant sa mise à jour. La valeur de cette expression est retournée à la variable table@MyTableVar dans la colonne 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

L'exemple suivant met à jour la colonne ScrapReasonID dans la table WorkOrder pour toutes les commandes avec ProductID et ScrapReasonID. La clause OUTPUT INTO retourne les valeurs depuis la table en cours de mise à jour (WorkOrder) ainsi que de la table Product. La table Product est utilisée dans la clause FROM pour spécifier les lignes à mettre à jour. Étant donné qu'un déclencheur AFTER UPDATE est défini sur la table WorkOrder, le mot clé INTO est obligatoire.

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

L'exemple suivant supprime les lignes dans la table ProductProductPhoto en fonction des critères de recherche définis dans la clause FROM de l'instruction DELETE. La clause OUTPUT retourne les colonnes de la table en cours de suppression (deleted.ProductID, deleted.ProductPhotoID) ainsi que les colonnes de la table Product. Cette table est utilisée dans la clause FROM pour spécifier les lignes à supprimer.

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

L'exemple suivant met à jour une valeur partielle dans DocumentSummary, une colonne nvarchar(max) de la table Production.Document, à l'aide de la clause .WRITE. Le terme components est remplacé par le terme features, en spécifiant le terme de remplacement, l'emplacement de départ (décalage) du terme à remplacer dans les données existantes et le nombre de caractères à remplacer (longueur). L'exemple utilise la clause OUTPUT pour retourner les images avant et après de la colonne DocumentSummary à la variable @MyTableVartable. Notez que les images complètes avant et après de la colonne DocumentSummary sont retournées.

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

L'exemple suivant utilise la clause OUTPUT dans un déclencheur pour retourner les résultats de l'opération de celui-ci. Tout d'abord, une vue est créée sur la table ScrapReason, puis un déclencheur INSTEAD OF INSERT est défini sur la vue qui ne permet qu'à la colonne Name de la table de base d'être modifiée par l'utilisateur. Étant donné que la colonne ScrapReasonID est une colonne IDENTITY de la table de base, le déclencheur ignore la valeur fournie par l'utilisateur. Ceci permet au moteur de base de données de générer automatiquement la bonne valeur. De plus, la valeur fournie par l'utilisateur pour ModifiedDate est ignorée et définie sur la date actuelle. La clause OUTPUT retourne les valeurs insérées dans la table 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

Voici l'ensemble de résultats généré le 12 avril 2004 ('2004-04-12'). Notez que les colonnes ScrapReasonIDActual et ModifiedDate illustrent les valeurs générées par l'opération du déclencheur plutôt que les valeurs fournies dans l'instruction INSERT.

ScrapReasonID  Name             ModifiedDate

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

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

L'exemple suivant crée la table EmployeeSales, puis y insère plusieurs lignes à l'aide d'une instruction INSERT avec une instruction SELECT pour extraire les données des tables sources. La table EmployeeSales contient une colonne d'identité (EmployeeID) et une colonne calculée (ProjectedSales). Étant donné que ces valeurs sont générées par le moteur de base de données SQL Server lors de l'insertion, aucune de ces colonnes ne peut être définie dans @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

L'exemple suivant supprime les lignes dans la table ProductProductPhoto en fonction des critères de recherche définis dans la clause FROM de l'instruction DELETE. La clause OUTPUT INTO retourne les colonnes de la table en cours de suppression (deleted.ProductID, deleted.ProductPhotoID), ainsi que les colonnes de la table Product à la variable table@MyTableVar. La table Product est utilisée dans la clause FROM pour spécifier les lignes à supprimer. La clause OUTPUT retourne à l'application appelante deleted.ProductID, les colonnes deleted.ProductPhotoID, la date et l'heure de suppression de la ligne de la table ProductProductPhoto.

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

L'exemple suivant capture les données retournées par la clause OUTPUT d'une instruction MERGE et insère ces données dans une autre table. L'instruction MERGE met quotidiennement à jour la colonne Quantity de la table ProductInventory, en fonction des commandes traitées dans la table SalesOrderDetail. Elle supprime également les lignes correspondant aux produits dont le stock passe à 0 ou moins. Cet exemple capture les lignes supprimées et les insère dans une autre table, ZeroInventory, qui effectue le suivi des produits en rupture de stock.

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;