La clause de SORTIE a été introduite dans la version SQL Server 2005. La clause OUTPUT renvoie les valeurs de chaque ligne affectée par une instruction INSERT, UPDATE ou DELETE. Il prend même en charge l’instruction MERGE, qui a été introduite dans SQL Server 2008. Le résultat de la clause OUTPUT peut être inséré dans une table séparée pendant l’exécution de la requête. Cette clause est le plus souvent utilisée à des fins d’audit. En l’utilisant, quelqu’un peut implémenter un processus personnalisé qui fonctionnerait comme CDC ou agirait de la même manière que CDC.

En outre, les résultats de la clause OUTPUT peuvent être renvoyés aux applications de traitement pour être utilisés dans des choses telles que les messages de confirmation, la journalisation et toute autre exigence d’application. Dans cet article, je vais fournir un ensemble d’exemples pour présenter l’utilisation de la clause OUTPUT dans la capture des résultats des lignes mises à jour dans une variable de table pour les instructions de mise à JOUR.

Mise en route

Pour suivre les exemples ci-dessous, vous devrez créer une table Department_SRC en exécutant le code T-SQL ci-dessous dans une base de données SQL Server. La section référencée contient Output_Update.fichier sql, qui inclut le code T-SQL pour essayer les exemples ci-dessous.

IF OBJECT_ID ('Department_SRC', 'U') IS NOT NULL DROP TABLE dbo.Department_SRC;CREATE TABLE .( IDENTITY(1,1) NOT NULL, varchar(50) NOT NULL, varchar(50) NOT NULL, NOT NULL) ON Insert into .(,,) Values('Engineering','Research and Development',getdate());

Vous pouvez voir dans les données du tableau que l’enregistrement a été inséré dans le tableau comme indiqué dans l’image ci-dessous.

Implémentez la clause de SORTIE dans les instructions de mise à jour

Comme nous l’avons discuté dans mon article précédent, la clause de SORTIE donne accès à deux tables virtuelles (tables magiques) :

1. « INSERTED” contient la nouvelle rows (INSERT ou UPDATE)
2. « DELETED” contient l’ancienne copie des lignes (UPDATE)

La clause OUTPUT avec une instruction UPDATE aura accès aux tables internes INSÉRÉES et SUPPRIMÉES. Cela signifie que ces deux tables sont accessibles en même temps que l’exécution de l’instruction UPDATE. Par exemple, supposons que nous mettons à jour quelques colonnes d’une table pour une seule ligne. Les valeurs nouvellement mises à jour sont les modifications apportées à la table et se trouvent dans la table interne INSÉRÉE. De plus, les données qui ont été modifiées sont les anciennes valeurs de données et sont insérées dans la table interne SUPPRIMÉE.

Affichage des valeurs de mise à jour

En utilisant la clause OUTPUT, nous pouvons afficher les valeurs mises à jour dans la fenêtre output en sélectionnant les noms de colonne avec le préfixe INSÉRÉ ou en utilisant INSERTED.* pour afficher toutes les colonnes de la table INSÉRÉE. De plus, nous pouvons afficher les anciennes valeurs de données de la table dans la fenêtre de sortie en sélectionnant les noms de colonnes avec préfixe SUPPRIMÉ ou en utilisant SUPPRIMÉ.* pour afficher toutes les colonnes de ce tableau. Cela aidera également à imprimer certains des messages de sortie sur la fenêtre de management studio.

Afin d’expliquer cela avec un exemple, j’exécute le code ci-dessous, dans lequel nous essayons de mettre à jour la colonne Nom de groupe de la valeur « Recherche et développement” à la valeur « Informatique” pour le département « Ingénierie”.

--Update the GroupName for the Department.Update Set ='IT', ModifiedDate=Getdate()OUTPUT deleted.Name ,deleted.GroupName as GroupName_old, deleted.ModifiedDate as ModifiedDate_old, inserted.GroupName as GroupName_new, inserted.ModifiedDate as ModifiedDate_newWhere ='Engineering'

Une fois que nous exécutons le code ci-dessus, nous pouvons voir le résultat de la clause de SORTIE comme indiqué dans l’image ci-dessous.

Insertion des résultats de SORTIE dans une variable de table

Afin de présenter cet exemple, j’ai déclaré une variable de table appelée @Updated pour stocker les résultats de la clause de SORTIE dans cette variable de table. Nous utilisons la syntaxe OUTPUT INTO afin de stocker les résultats dans une variable de table. Dans cette approche, nous avons accès à définir des lignes à la fois les nouvelles valeurs et les anciennes valeurs qui ont été mises à jour par la requête, qui peuvent être utilisées pour les étapes suivantes dans le même lot.

--capturing Updated values using table variable.DECLARE @Updated table( int, varchar(50), varchar(50), varchar(50), datetime, datetime);Update Set ='IT', ModifiedDate=Getdate()OUTPUT deleted.DepartmentID, deleted.Name, deleted.GroupName as GroupName_old, inserted.GroupName as GroupName_new, deleted.ModifiedDate as ModifiedDate_old,inserted.ModifiedDate as ModifiedDate_newINTO @UpdatedWhere ='Engineering'--Querying from @Updated output tableSelect * from @Updated

Une fois que nous avons exécuté le code ci-dessus, nous pouvons voir que les résultats ont été insérés dans la variable de table @Updated comme indiqué dans l’image ci-dessous.

Utilisation de la clause OUTPUT pour Charger une dimension de TYPE 3 Changeant lentement

Avec les exemples ci-dessus, la clause OUTPUT est une très bonne alternative pour charger une table de dimensions de TYPE 3. Afin d’expliquer cela avec un exemple, j’exécute le code ci-dessous, qui créera une table de dimension de TYPE 3 appelée Department_Type3.

---Type 3 table exampleIF OBJECT_ID ('Department_Type3', 'U') IS NOT NULL DROP TABLE dbo.Department_Type3;CREATE TABLE .( IDENTITY(1,1) NOT NULL, varchar(50) NULL, varchar(50) NULL, varchar(50) NULL, NULL) ON GO---Insert some test valuesInsert into .(,,) Values('Engineering','Research and Development',getdate());Select * from .

Une fois que nous exécutons le code ci-dessus, nous pouvons voir le résultat des données de la table comme indiqué dans l’image ci-dessous.

Lorsque nous insérons un nouvel enregistrement dans la table Department_Type3, nous pouvons voir que la colonne GroupName_old a une valeur NULLE et GroupName_Current a la valeur de l’insertion récente. Supposons qu’il y ait eu un changement dans GroupName_current pour le nom du département « Ingénierie” et nous devons mettre à jour la table Department_Type3 avec la nouvelle valeur GroupName_current. Comme il s’agit d’une table de dimensions de TYPE3, l’exigence ici est de mettre à jour la colonne GroupName_old avec « Recherche et développement” et GroupName_Current avec la nouvelle valeur.

--capturing Updated values using table variable.DECLARE @Updated table( int, varchar(50), varchar(50), varchar(50), datetime, datetime);Update Set ='IT', EffectiveDate=Getdate()OUTPUT deleted.DepartmentID, deleted.Name, deleted. as ,inserted.GroupName_current as GroupName_new, deleted.EffectiveDate as ModifiedDate_old,inserted.EffectiveDate as ModifiedDate_newINTO @UpdatedWhere ='Engineering'--Update the GroupName_old with old valuesUpdate aSet a.GroupName_old=b.GroupName_old, a.EffectiveDate=b.ModifiedDate_newfrom as ainner join @Updated as bon a.DepartmentID=b.DepartmentID--Querying the final tableSelect * from 

Une fois que nous aurons exécuté le code ci-dessus, nous capturerons les résultats de la table interne SUPPRIMÉE dans la variable de table @Updated, puis nous utiliserons ces données pour mettre à jour les colonnes de la table finale pour implémenter cette dimension de TYPE 3 changeant lentement. Nous pouvons voir le résultat des données du tableau final avant et après l’exécution de l’instruction ci-dessus, comme indiqué dans les images ci-dessous.

Avant l’exécution de l’instruction UPDATE

Après l’exécution de l’instruction UPDATE

Limitations de la clause de sortie

Certains des scénarios où la clause de SORTIE n’est pas prise en charge :

1. Instructions DML qui font référence aux vues partitionnées locales, aux vues partitionnées distribuées ou aux tables distantes.
2. INSÉRER des instructions qui contiennent une instruction EXECUTE.
3. Les prédicats en texte intégral ne sont pas autorisés dans la clause OUTPUT lorsque le niveau de compatibilité de la base de données est défini sur 100.
4. La clause OUTPUT INTO ne peut pas être utilisée pour insérer dans une vue ou une fonction de jeu de lignes.
5. Une fonction définie par l’utilisateur ne peut pas être créée si elle contient une clause OUTPUT INTO qui a une table comme cible

La liste complète est répertoriée dans ce lien : https://technet.microsoft.com/en-us/library/ms177564(v=sql.110).aspx

Résumé

Comme indiqué dans les exemples ci-dessus, nous pouvons voir que la clause OUTPUT est facile à utiliser et peut éviter beaucoup de codage personnalisé afin de capturer les résultats de sortie des valeurs mises à jour avec les anciennes et les nouvelles valeurs dans une variable de table pendant l’exécution de la requête.