T-SQL prend en charge la clause OUTPUT après la création de SQL Server 2005 et des éditions ultérieures. Nous pouvons utiliser la clause OUTPUT avec des instructions DML (INSERT, DELETE, UPDATE) pour renvoyer des informations à partir de lignes modifiées.
Nous utilisons principalement la clause OUTPUT pour l'audit et l'archivage des lignes modifiées. Dans ce tutoriel, nous allons passer en revue l'utilisation de la clause OUTPUT avec différentes instructions et exemples DML. Tout d'abord, nous allons créer une table, dbo.Songs, et le remplir avec des données.
SI OBJECT_ID ('dbo.Songs') N'EST PAS NUL DROP TABLE dbo.Songs GO CREATE TABLE dbo.Songs (Id int CONSTRAINT PK_Songs_Id PRIMARY KEY, Nom varchar (200) NON NULL, Chanteur varchar (50) NON NULL) GO INSERT INTO dbo.Songs (Identifiant, Nom, Chanteur) VALEURS (1, "Je déteste tout ce qui vous concerne", "Adam Gontier"); INSERT INTO dbo.Songs (Id, Nom, Chanteur) VALEURS (2, 'Dil se', 'A. R. Rahman'); INSERT INTO dbo.Songs (Identifiant, Nom, Chanteur) VALEURS (3, 'Mon coeur va continuer', 'Céline Dion'); INSERT INTO dbo.Songs (Id, Nom, Chanteur) VALEURS (4, 'Maeri', 'Euphoria'); GO SELECT * de dbo.Songs GO
Tables insérées et supprimées dans une clause OUTPUT
Les tables insérées et supprimées sont deux tables résidant en mémoire qui résident dans le serveur SQL et sont utilisées avec la clause OUTPUT.
Chaque fois qu'une instruction DML (INSERT, DELETE, UPDATE) est exécutée, ces tables sont remplies.
Les résultats de l'instruction INSERT sont stockés dans la table insérée et les résultats de l'instruction Delete sont stockés dans la table supprimée. De plus, avec une instruction UPDATE, les lignes supprimées sont stockées dans la table Supprimée. Les nouvelles lignes insérées dans la table insérée en tant que UPDATE ne sont que des opérations de suppression et d’insertion combinées.
Remarque: vous ne pouvez pas interroger directement les tables insérées et supprimées pour connaître les données qu'elles contiennent actuellement, mais vous pouvez les utiliser avec la clause OUTPUT ainsi qu'avec les déclencheurs.
La clause OUTPUT avec une instruction Insert
Lorsque nous effectuons une opération Insert sur une table, nous obtenons un message indiquant «(n ligne (s) affectée (s))», mais si nous voulons voir quelles lignes de données ont été insérées dans une table, nous pouvons utiliser une clause OUTPUT et une table insérée dans la mémoire pour retourner les résultats à l'écran de la même manière qu'une instruction select.
Insérons un enregistrement et utilisons une clause OUTPUT pour imprimer les résultats à l'écran.
INSERT INTO dbo.Songs (Identifiant, Nom, Chanteur) OUTPUT INSERTED.ID, INSERTED.name, INSERTED.Singer VALUES (5, 'Pas de tombe', 'Johnny Cash'); ALLER
Vérifiez la table dbo.Songs. Une nouvelle ligne est insérée avec id = 5.
sélectionnez * parmi dbo.Songs; ALLER
La clause OUTPUT avec une instruction de suppression
Il en va de même avec une opération de suppression. Il montre seulement (n lignes affectées). Nous pouvons utiliser une clause OUTPUT et une table supprimée pour voir quelles lignes ont été réellement supprimées de la table.
DELETE DE dbo.Songs OUTPUT DELETED.id, DELETED.name, DELETED.singer WHERE ID = 5; ALLER
Interrogez la ligne de la table dbo.Songs avec id = 5 a été supprimée.
sélectionnez * parmi dbo.Songs; ALLER
La clause OUTPUT avec une instruction de mise à jour
Une instruction Update ne fait que supprimer les anciennes données et insérer de nouvelles données. Ainsi, avec une instruction Update, les deux tables résidentes en mémoire sont affectées et sont supprimées et insérées.
Ici, nous mettons à jour le nom d'un chanteur qui a chanté «Dil se», avec un ID égal à deux.
UPDATE dbo.Songs SET Singer = 'Rahman' OUTPUT DELETED.Singer, INSERTED.Singer WHERE ID = 2; ALLER
Vous pouvez voir le nom de l'ancien chanteur avec le nom du nouveau chanteur.
sélectionnez * parmi dbo.Songs;
Les trois exemples ci-dessus montrent comment utiliser une clause OUTPUT à des fins d'audit. Nous allons maintenant voir comment l'utiliser pour l'archivage.
Auparavant, nous imprimions simplement les résultats d'une instruction DML à l'écran, ce qui était temporaire, mais avec la clause OUTPUT, vous pouvez également stocker les résultats d'une instruction DML dans une table.
Stocker les résultats d'une clause OUTPUT dans une table
L'insertion du retour de données d'une clause OUTPUT dans une table peut être effectuée à l'aide d'une clause OUTPUT INTO. Gardez à l'esprit que vous devez d'abord créer la table cible qui doit avoir le même nombre de colonnes et de types de données qui correspondent à la table source.
SI OBJECT_ID ('dbo.Songs_Inserted') N'EST PAS NUL DROP TABLE dbo.Songs_Inserted GO CREATE TABLE dbo.Songs_Inserted (Id int CONSTRAINT PK_Songs__Inserted_Id, PRIMARY KEY, nom varchar (200) NON NULL, Singer Divers) dbo.Songs (Id, Nom, Chanteur) OUTPUT inséré. * INTO dbo.Songs_Inserted VALUES (5, 'Duniya', 'Piyush Mishra'); GO - Résultat de la table Songs_Inserted et de la table de base. sélectionnez * parmi dbo.Songs_Inserted; sélectionnez * parmi dbo.Songs; ALLER
Comme le montrent les résultats ci-dessus, des données sont insérées dans les deux tableaux.
Stocker les résultats d'une clause OUTPUT dans une table temporaire
La même chose va avec une table temporaire. Créez d'abord une table temporaire, puis à l'aide d'une clause OUTPUT INTO, insérez les données renvoyées par la clause OUTPUT dans une table temporaire.
SI OBJECT_ID ('tempdb… #Songs_Deleted') N'EST PAS NUL DROP TABLE dbo. # Songs_Deleted GO CREATE TABLE dbo. # Songs_Deleted (Id int, Nom varchar (200) NOT NULL, Singer varchar (50) NON NULL) GO DELETE à partir de dbo .Songs OUTPUT supprimé. * INTO dbo. # Songs_Deleted WHERE ID IN (4,5); GO - Résultat de la table temporaire et de la table de base. SELECT * from dbo. # Songs_Deleted; Sélectionnez * parmi dbo.Songs;
Stocker les résultats d'une clause OUTPUT dans une variable de table
Rien ne change également pour les variables de table. Déclarez une structure de variable de table identique à une table source. N'oubliez pas d'exécuter le script entier en même temps pour voir le résultat inséré dans une variable de table.
Déclarez @Songs_Deleted TABLE (Id int, Nom varchar (200) NON NULL, Singer varchar (50) NON NULL) SUPPRIMER de dbo.Songs OUTPUT supprimé. - Résultat de la variable de table SELECT * from @Songs_Deleted;
Parcourez nos articles sur les archives SQL Server pour plus d’informations utiles.