Partager via

Clause OUTPUT (Transact-SQL)

  • Article

S’applique à : SQL Server Azure SQL Database Azure SQL Managed Instance Base de données SQL dans Microsoft Fabric

Retourne des informations provenant, ou des expressions basées sur, sur chaque ligne affectée par une INSERTinstruction , ou DELETEMERGE une UPDATEinstruction. Ces résultats peuvent être retournés à l'application en cours de traitement afin d'être utilisés notamment avec des messages de confirmation, des opérations d'archivage et d'autres spécifications d'application similaires. Il est également possible d'insérer ces résultats dans une table ou dans une variable de table. En outre, vous pouvez capturer les résultats d’une OUTPUT clause dans une INSERTinstruction, UPDATEune instruction ou MERGE DELETEune table imbriquée, et insérer ces résultats dans une table ou une vue cible.

Remarque

Une UPDATEinstruction , INSERTou DELETE qui a une OUTPUT clause retourne des lignes au client même si l’instruction rencontre des erreurs et est restaurée. Le résultat ne doit pas être utilisé si des erreurs surviennent quand vous exécutez l’instruction.

Utilisation dans :

Conventions de la syntaxe Transact-SQL

Syntaxe

<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

Arguments

@table_variable

Spécifie une variable table dans laquelle les lignes retournées sont insérées au lieu d’être retournées à l’appelant. @table_variable doivent être déclarés avant l’instruction INSERT, UPDATEle DELETE, ou MERGE l’instruction.

Si column_list n’est pas spécifié, la variable table doit contenir le même nombre de colonnes que le jeu de résultats OUTPUT. Les exceptions sont des colonnes calculées et des colonnes d'identité qui doivent être ignorées. Si column_list est spécifié, toutes les colonnes ignorées doivent autoriser des valeurs NULL ou avoir des valeurs par défaut.

Pour plus d’informations sur les variables de table , consultez la table.

output_table

Spécifie une table dans laquelle les lignes retournées sont insérées au lieu d'être retournées à l'appelant. output_table peut être une table temporaire.

Si column_list n’est pas spécifié, la variable table doit contenir le même nombre de colonnes que le jeu de résultats OUTPUT. Les exceptions sont des colonnes calculées et des colonnes d'identité qui doivent être ignorées. Si column_list est spécifié, toutes les colonnes ignorées doivent autoriser des valeurs NULL ou avoir des valeurs par défaut.

output_table ne peut pas :

  • Avoir de déclencheurs activés.
  • Participez de l’un ou l’autre côté d’une FOREIGN KEY contrainte.
  • Avoir CHECK des contraintes ou des règles activées.

column_list

Liste facultative des noms de colonnes sur la table cible de la INTO clause. Il est analogue à la liste de colonnes autorisée dans l’instruction INSERT .

scalar_expression

Combinaison de symboles et d’opérateurs qui renvoie une valeur unique. Les fonctions d’agrégation ne sont pas autorisées dans scalar_expression.

Toute référence aux colonnes de la table en cours de modification doit être qualifiée avec le ou DELETED le INSERTED préfixe.

column_alias_identifier

Autre nom utilisé pour faire référence au nom de colonne.

DELETED

Préfixe de colonne qui spécifie la valeur supprimée par l’opération de mise à jour ou de suppression, ainsi que les valeurs existantes qui ne changent pas avec l’opération actuelle. Les colonnes précédées de DELETED la valeur reflètent la valeur avant la fin de l’instruction , DELETEou MERGE de l’instructionUPDATE.

DELETED ne peut pas être utilisé avec la OUTPUT clause de l’instruction INSERT .

INSERTED

Préfixe de colonne qui spécifie la valeur ajoutée par l’opération d’insertion ou de mise à jour et toutes les valeurs existantes qui ne changent pas avec l’opération actuelle. Les colonnes précédées de INSERTED refléter la valeur après la fin de l’instruction ou INSERTde MERGE l’instruction UPDATEsont terminées, mais avant l’exécution des déclencheurs.

INSERTED ne peut pas être utilisé avec la OUTPUT clause de l’instruction DELETE .

from_table_name

Préfixe de colonne qui spécifie une table incluse dans la FROM clause d’un DELETE, UPDATEou MERGE instruction utilisée pour spécifier les lignes à mettre à jour ou supprimer.

Si la table en cours de modification est également spécifiée dans la FROM clause, toute référence aux colonnes de cette table doit être qualifiée avec le ou DELETED le INSERTED préfixe.

*

L’astérisque (*) spécifie que toutes les colonnes affectées par l’action de suppression, d’insertion ou de mise à jour sont retournées dans l’ordre dans lequel elles existent dans la table.

Par exemple, dans l’instruction suivanteDELETE, OUTPUT DELETED.* toutes les colonnes supprimées de la ShoppingCartItem table sont retournées :

DELETE Sales.ShoppingCartItem
    OUTPUT DELETED.*;

column_name

Référence de colonne explicite. Toute référence à la table en cours de modification doit être correctement qualifiée par le préfixe ou le INSERTED DELETED préfixe approprié, par exemple : INSERTED.<column_name>.

$action

Disponible uniquement pour l’instruction MERGE . Spécifie une colonne de type nvarchar(10) dans la OUTPUT clause d’une MERGE instruction qui retourne l’une des trois valeurs de chaque ligne : INSERT, UPDATEou DELETE, en fonction de l’action effectuée sur cette ligne.

Notes

La OUTPUT <dml_select_list> clause et la OUTPUT <dml_select_list> INTO { @table_variable | output_table } clause peuvent être définies dans une instruction INSERTunique, ou UPDATEDELETEMERGE une instruction.

Remarque

Sauf indication contraire, les références à la clause OUTPUT sont relatives aux deux clauses OUTPUT et OUTPUT INTO.

La OUTPUT clause peut être utile pour récupérer la valeur des colonnes d’identité ou calculées après une opération ou UPDATE une INSERT opération.

Quand une colonne calculée est incluse dans <dml_select_list>, la colonne correspondante dans la table de sortie ou la variable de table n’est pas une colonne calculée. Les valeurs de la nouvelle colonne sont les valeurs calculées lors de l'exécution de l'instruction.

L’ordre dans lequel les modifications sont appliquées à la table, et l’ordre dans lequel les lignes sont insérées dans la table de sortie ou la variable de table, ne sont pas garanties de correspondre.

Si des paramètres ou des variables sont modifiés dans le cadre d’une UPDATE instruction, la OUTPUT clause retourne toujours la valeur du paramètre ou de la variable telle qu’elle était avant l’exécution de l’instruction au lieu de la valeur modifiée.

Vous pouvez utiliser OUTPUT avec une UPDATE instruction ou DELETE une instruction positionnée sur un curseur qui utilise WHERE CURRENT OF la syntaxe.

La clause OUTPUT n’est pas prise en charge dans les instructions suivantes :

  • Instructions DML faisant référence aux vues partitionnées locales, aux vues partitionnées distribuées ou aux tables distantes.

  • INSERT instructions qui contiennent une EXECUTE instruction.

  • Les prédicats de texte intégral ne sont pas autorisés dans la clause OUTPUT quand le niveau de compatibilité de la base de données a la valeur 100.

  • La clause OUTPUT INTO ne peut pas être utilisée pour insérer une vue ou une fonction d’ensemble de lignes.

  • Une fonction définie par l’utilisateur ne peut pas être créée si elle contient une OUTPUT INTO clause qui a une table comme cible.

Pour empêcher tout comportement non déterministe, la clause OUTPUT ne peut pas contenir les références suivantes :

  • Les sous-requêtes ou les fonctions définies par l'utilisateur qui permettent d'accéder à des données utilisateur ou système, ou qui sont supposées permettre d'y accéder. Les fonctions définies par l’utilisateur sont supposées permettre d’accéder à des données si elles ne sont pas liées au schéma.

  • Une colonne d'une vue ou fonction de table inline lorsque cette colonne est définie par l'une des méthodes suivantes :

    • Une sous-requête.

    • Une fonction définie par l'utilisateur qui offre un accès à des données utilisateur ou système, ou qui est supposée permettre d'y accéder.

    • Une colonne calculée qui contient une fonction définie par l’utilisateur qui effectue un accès aux données utilisateur ou système dans sa définition.

    Quand SQL Server détecte ce type de colonne dans la clause OUTPUT, l’erreur 4186 est levée.

Insérer les données retournées à partir d’une clause OUTPUT dans une table

Lorsque vous capturez les résultats d’une OUTPUT clause dans une instruction imbriquée INSERT, UPDATEou DELETEMERGE une instruction et que vous insérez ces résultats dans une table cible, gardez à l’esprit les informations suivantes :

  • L'opération entière est atomique. Soit l’instruction et l’instruction INSERT DML imbriquée qui contient la OUTPUT clause execute, soit l’instruction entière échoue.

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

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

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

    • Il n’est pas possible de définir des déclencheurs sur la cible.

    • La cible ne peut pas participer à la réplication de fusion ni à 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 être ni une table distante ni une vue partitionnée.

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

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

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

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

  • Les notifications de requête traitent l’instruction en tant qu’entité unique, et le type de message créé est le type du DML imbriqué, même si la modification significative provient de l’instruction externe INSERT elle-même.

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

Parallélisme

Une OUTPUT clause qui retourne des résultats au client ou à la variable de table utilise toujours un plan série.

Dans le contexte d’une base de données définie sur le niveau de compatibilité 130 ou supérieur, si une INSERT...SELECT opération utilise un WITH (TABLOCK) indicateur pour l’instruction SELECT et utilise OUTPUT...INTO également pour insérer dans une table temporaire ou utilisateur, la table cible pour l’objet INSERT...SELECT est éligible au parallélisme en fonction du coût de la sous-arborescence. La table cible référencée dans la OUTPUT INTO clause n’est pas éligible au parallélisme.

Déclencheurs

Les colonnes retournées à partir de OUTPUT reflètent les données telles qu’elles se trouvent après la fin de l’instruction , UPDATEou DELETE de l’instructionINSERT, mais avant l’exécution des déclencheurs.

Pour INSTEAD OF les déclencheurs, les résultats retournés sont générés comme si le INSERT, UPDATEou DELETE s’était réellement produit, même si aucune modification n’a lieu à la suite de l’opération de déclenchement. Si une instruction qui inclut une OUTPUT clause est utilisée dans le corps d’un déclencheur, les alias de table doivent être utilisés pour référencer les tables insérées et supprimées pour éviter de dupliquer les références de colonne avec les INSERTED tables DELETED associées à OUTPUT.

Si la OUTPUT clause est spécifiée sans également spécifier le INTO mot clé, la cible de l’opération DML ne peut pas avoir de déclencheur activé défini dessus pour l’action DML donnée. Par exemple, si la OUTPUT clause est définie dans une UPDATE instruction, la table cible ne peut pas avoir de déclencheurs activés UPDATE .

Si l’option sp_configure interdit les résultats des déclencheurs est définie, une OUTPUT clause sans INTO clause provoque l’échec de l’instruction lorsqu’elle est appelée à partir d’un déclencheur.

Types de données

La clause OUTPUT prend en charge les types d’objets volumineux suivants : nvarchar(max), varchar(max), varbinary(max), text, ntext, image et xml. Lorsque vous utilisez la .WRITE clause 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 être utilisée dans une expression sur une colonne text, ntext ou image dans la clause OUTPUT.

Files d’attente

Vous pouvez utiliser OUTPUT dans les applications utilisant les tables comme files d’attente ou garder les jeux 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 OUTPUT clause dans une DELETE instruction pour renvoyer la ligne supprimée à l’application appelante.

USE AdventureWorks2022;
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 implémentées, comme 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. Il appartient à l’application d’inclure une clause appropriée WHERE qui peut garantir la sémantique souhaitée ou comprendre que lorsque plusieurs lignes peuvent se qualifier pour l’opération DML, il n’existe aucune commande garantie. 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;

Voici les résultats :

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

Remarque

Utilisez l’indicateur READPAST de table et UPDATE DELETE les instructions si votre scénario permet à plusieurs applications d’effectuer une lecture destructrice à partir d’une table. Ce cas de figure empêche les problèmes de verrouillage qui peuvent survenir si une autre application est déjà entrain de lire le premier enregistrement dans la table.

autorisations

SELECT les autorisations sont requises sur toutes les colonnes récupérées ou <dml_select_list> utilisées dans <scalar_expression>.

INSERT les autorisations sont requises sur toutes les tables spécifiées dans <output_table>.

Exemples

Les exemples de code Transact-SQL de cet article sont fondés sur l’échantillon de base de données AdventureWorks2022 ou AdventureWorksDW2022 fourni, que vous pouvez télécharger à partir de la page d’accueil Échantillons et projets communautaires Microsoft SQL Server.

R : Utiliser OUTPUT INTO avec une instruction INSERT

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 de table @MyTableVar. É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, 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 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. Utiliser OUTPUT avec une instruction DELETE

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, c’est-à-dire toutes les colonnes dans les lignes supprimées, sont retournés à l’application appelante. L'instruction SELECT suivante vérifie les résultats de l'opération de suppression dans la table 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. Utiliser OUTPUT INTO avec une instruction UPDATE

L'exemple suivant met à jour la colonne VacationHours à hauteur de 25 % pour 10 premières lignes aléatoires dans la table Employee. La clause OUTPUT retourne la valeur 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 sur la variable de table @MyTableVar.

Deux instructions SELECT suivent. Elles retournent les valeurs dans @MyTableVar, ainsi que les résultats de la mise à jour dans la table 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. Utiliser OUTPUT INTO pour retourner une expression

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 de table @MyTableVar dans la colonne 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. Utiliser OUTPUT INTO avec from_table_name dans une instruction UPDATE

L’exemple suivant met à jour la colonne ScrapReasonID dans la table WorkOrder pour toutes les commandes avec les valeurs ProductID et ScrapReasonID spécifiées. 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 WorkOrder est défini sur la table AFTER UPDATE, le mot clé INTO est obligatoire.

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. Utiliser OUTPUT INTO avec from_table_name dans une instruction DELETE

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 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. Utiliser OUTPUT INTO avec un type de données d’objets volumineux

L’exemple suivant met à jour une valeur partielle dans DocumentSummary, une colonne nvarchar(max) dans la Production.Document table, à l’aide de la .WRITE clause. Le terme components est remplacé par le terme features, en spécifiant le terme de remplacement, la position 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 de table @MyTableVar. Les images complètes avant et après la colonne DocumentSummary sont retournées.

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. Utiliser OUTPUT dans un déclencheur INSTEAD OF

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 valeur correcte. 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 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

Voici l'ensemble de résultats généré le 12 avril 2004 ('2004-04-12'). Les ScrapReasonIDActual colonnes et ModifiedDate les colonnes reflètent les valeurs générées par l’opération de déclencheur au lieu des valeurs fournies dans l’instruction INSERT .

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

I. Utiliser OUTPUT INTO avec des colonnes d’identité et des colonnes calculées

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 récupérer les données des tables sources. La table EmployeeSales contient une colonne d'identité (EmployeeID) et une colonne calculée (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. Utiliser OUTPUT et OUTPUT INTO dans une instruction unique

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), et les colonnes de la table Product à la variable de table @MyTableVar. La table Product est utilisée dans la clause FROM pour spécifier les lignes à supprimer. La OUTPUT clause retourne les colonnes, DELETED.ProductPhotoID ainsi que la DELETED.ProductIDdate et l’heure à laquelle la ligne a été supprimée de la ProductProductPhoto table vers l’application appelante.

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. Insérer des données retournées à partir d’une clause OUTPUT

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 une valeur inférieure. 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 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

Contenu connexe