La clause de sortie SQL Server renvoie les valeurs de chaque ligne affectée par une instruction INSERT, UPDATE ou DELETE.
La clause Output renvoie une copie des données qui peuvent être insérées dans une table séparée pendant l’exécution de la requête.
Cette clause est le plus souvent utilisée pour suivre les changements de données dans le tableau à des fins d’audit.
La clause OUTPUT donne accès à deux tables virtuelles comme indiqué ci-dessous.
- INSERTED qui contient les nouvelles lignes (INSERT ou UPDATE)
- DELETED qui contient l’ancienne copie des lignes (vide pour INSERT)
La clause OUTPUT peut accéder à toutes les colonnes de la table, même celles-ci ne font pas partie de l’instruction Insert.
SYNTAXE
---- for insertINSERT INTO tablename OUTPUT inserted . *INTO @output_tablenameVALUES (col_value1 ,col_value2 ,..) ---for delete DELETE FROM tablenameOUTPUT deleted.* INTO @output_tablename WHERE Condition
CREATE TABLE dbo.Item (ItemId Int identity(1,1),ItemName Varchar(20),ItemPurchasedDate Datetime)
Utilisation de la clause de sortie avec l’instruction Insert
Pour insérer le résultat de la clause de sortie dans une table, Déclarez d’abord une variable de table pour stocker le résultat de la clause de sortie et utilisez la syntaxe de caluse de sortie pour stocker le résultat dans la variable de table.
Pour accéder aux enregistrements insérés, la clause Output utilise une table virtuelle insérée contenant les nouvelles lignes.
Dans l’instruction suivante, nous insérons un enregistrement dans la table des éléments et utilisons la clause output pour stocker ces enregistrements dans une variable de table @insert_log comme indiqué ci-dessous
DECLARE @insert_log TABLE(ItemId Int, ItemName Varchar(20), ItemPurchasedDate Datetime )INSERT INTO dbo.Item OUTPUT inserted.ItemId, inserted.ItemName, inserted.ItemPurchasedDateINTO @insert_logVALUES ('Item1' , Getdate()) SELECT * FROM dbo.Item;SELECT * FROM @insert_log ;
Comme vous pouvez voir la sortie de la table d’éléments et de la variable de table, Lors de l’insertion de nouveaux enregistrements dans la table d’éléments, ItemId ne fait pas partie de l’instruction insert. Cependant, vous pouvez accéder à la colonne ItemId dans la clause OUTPUT.
Utilisation de l’instruction Output caluse with Update
Pour accéder aux enregistrements mis à jour, vous pouvez utiliser une table virtuelle insérée ou supprimée avec une clause output selon vos besoins.
Si vous souhaitez accéder à une ancienne valeur avant la mise à jour de la nouvelle valeur, vous pouvez utiliser la table virtuelle supprimée et si vous souhaitez accéder à de nouvelles valeurs mises à jour, vous pouvez utiliser la table virtuelle insérée avec la clause de sortie.
Dans l’instruction suivante, nous mettons à jour la valeur ItemName dans la table Item et utilisons la clause output pour stocker les anciennes valeurs de ces enregistrements dans une variable de table @update_log.
Comme nous voulons accéder aux anciens enregistrements, nous utilisons une table virtuelle supprimée.
DECLARE @update_log TABLE( ItemId int, ItemName Varchar(20), ItemPurchasedDate Datetime )UPDATE dbo.Item SET ItemName ='Item2'OUTPUT deleted.ItemId, deleted.ItemName, deleted.ItemPurchasedDateINTO @update_logWHERE ItemId =1SELECT * FROM dbo.Item;SELECT * FROM @update_log
Comme vous pouvez le voir, nous avons ici trois sorties comme suit
1: C’est la sortie de la table avant de mettre à jour les enregistrements.
2: C’est la sortie de la table après la mise à jour des enregistrements.
3: C’est la sortie renvoyée par la variable de table qui contient les anciennes valeurs d’enregistrements qui étaient avant la mise à jour.
Si vous souhaitez accéder à de nouveaux enregistrements, vous pouvez utiliser la table virtuelle insérée à la place de celle supprimée.
Clause de sortie avec instruction Delete
L’instruction suivante utilise la requête delete pour supprimer l’enregistrement pour ItemId = 1 de la table d’éléments et stocker les enregistrements supprimés dans la variable de table à l’aide de la caluse de sortie.
Pour accéder à la clause de sortie des enregistrements supprimés, utilisez la table virtuelle supprimée qui contient l’ancienne copie des lignes.
DECLARE @delete_log TABLE( ItemId Int, ItemName Varchar(20), ItemPurchasedDate Datetime )DELETE FROM dbo.Item OUTPUT deleted.ItemId, deleted.ItemName, deleted.ItemPurchasedDateINTO @delete_logWHERE ItemId =1SELECT * FROM dbo.Item;SELECT * FROM @delete_log
Comme vous pouvez le voir, aucune donnée n’est disponible dans la table des éléments car elle est supprimée, mais la clause de sortie insère les enregistrements supprimés dans une variable de table @delete_log, qui est très utile pour garder une trace des enregistrements supprimés.
Recommandé pour vous
Tables temporelles SQL Server
822 vues au total, 8 vues aujourd’hui