Problème
Parfois, nous avons besoin de comparer les tables et/ou les données de SQL Server pour savoir ce qui a changé. Cet article présente différentes méthodes pour comparer les données, les types de données et les structures de table lors de l’utilisation de SQL Server.
Solution
Je vais aborder différentes méthodes pour identifier les changements en utilisant différentes requêtes SQL ainsi que quelques outils de développement.
Supposons que nous disposions de deux tables similaires dans des bases de données différentes et que nous voulions savoir ce qui les différencie. Voici un script qui crée des bases de données, des tables et des données d’exemple.
Le code T-SQL génère 2 tables dans des bases de données différentes. Les noms des tables sont les mêmes, mais la table dans la base de données dbtest02 contient une ligne supplémentaire comme indiqué ci-dessous.
Voyons comment nous pouvons comparer ces tables en utilisant différentes méthodes.
Comparer les données SQL Server dans les tables en utilisant une jointure gauche
Avec une jointure gauche, nous pouvons comparer les valeurs de colonnes spécifiques qui ne sont pas communes entre deux tables.
Par exemple, avec cette instruction SELECT :
SELECT *
FROM dbtest02.dbo.article A
LEFT JOIN dbtest01.dbo.article B ON A.id = B.idL’ensemble de résultats de la jointure gauche affiche toutes les lignes de la table de gauche “dbtest02.dbo.article”, même s’il n’y a pas de correspondance dans la table “dbtest01.dbo.article”.
Dans cet exemple, nous comparons 2 tables et les NULL sont affichés s’il n’y a pas de lignes correspondantes. Cette méthode fonctionne pour vérifier les nouvelles lignes, mais si nous mettons à jour d’autres colonnes, la jointure gauche ne sera pas utile.
Cela peut être fait dans les deux sens pour voir s’il y a des différences dans l’autre sens. Cette instruction SQL ne renverra que les 3 lignes correspondantes.
Comparaison des données SQL Server dans les tables en utilisant la clause EXCEPT
EXCEPT montre la différence entre deux tables (les gars du DBMS Oracle utilisent “minus” au lieu de “except” et la syntaxe et l’utilisation sont les mêmes). Elle est utilisée pour comparer les différences entre deux tables. Par exemple, voyons les différences entre les deux tables :
SELECT *
FROM dbtest01.dbo.article
EXCEPT
SELECT *
FROM dbtest02.dbo.articleEXCEPT renvoie la différence entre les tables de dbtest02 et dbtest01.
Si nous inversions les tables dans la requête, nous ne verrions aucun enregistrement, car la table dans la base de données dbtest02 contient tous les enregistrements, plus un supplémentaire.
Cette méthode est meilleure que la première, car si nous changeons les valeurs d’autres colonnes comme le type et le coût, EXCEPT remarquera la différence.
Voici un exemple si nous mettons à jour l’identifiant “001” dans la base de données dbtest01 et changeons le coût de “40” à “1”. Si nous mettons à jour les enregistrements, puis exécutons à nouveau la requête, nous verrons ces différences maintenant :
Malheureusement, cela ne crée pas un script pour synchroniser les tables.
Comparer les données SQL Server dans les tables en utilisant l’outil Tablediff
Il s’agit d’un outil en ligne de commande gratuit utilisé pour comparer les tables. Il se trouve dans le dossier “C:Program FilesMicrosoft SQL Server110COM”. Cet outil en ligne de commande est utilisé pour comparer les tables. Il génère également un script avec les instructions INSERT, UPDATE et DELETE pour synchroniser les tables. Pour plus de détails, consultez cet article sur la différence de table.
Comparer les données SQL Server dans les tables en utilisant Change Data Capture (CDC)
Cette fonctionnalité est disponible dans SQL Server 2008 et ultérieure. Vous devez activer cette fonctionnalité et vous devez également avoir l’Agent SQL Server en cours d’exécution. Fondamentalement, elle crée des tables système qui suivent les modifications apportées aux tables que vous souhaitez surveiller. Elle ne compare pas les tables, mais elle suit les modifications dans les tables.
Pour plus d’informations, consultez ces astuces sur Change Data Capture (CDC).
Comparer les types de données SQL Server entre deux tables
Que se passe-t-il si nous voulons comparer les types de données ? Existe-t-il un moyen de comparer les types de données ?
Oui, nous pouvons utiliser les vues système [INFORMATION_SCHEMA].[COLUMNS] pour vérifier et comparer les informations. Nous allons créer une nouvelle table nommée dbo.article2 avec une colonne ayant un type de données différent de la table dbo.article :
La différence est que l’identifiant est maintenant de type “int” au lieu de “nchar(10)” comme dans les autres tables.
La requête pour comparer les types de données des tables article et article1 serait :
Les résultats sont les suivants :
La requête compare les types de données de ces deux tables. Toutes les informations des colonnes peuvent être obtenues à partir de la vue système [INFORMATION_SCHEMA].[COLUMNS]. Nous comparons la table “article” avec la table “article2” et affichons si l’un des types de données est différent.
Comparer s’il y a des colonnes supplémentaires entre les tables de bases de données SQL Server
Parfois, nous devons nous assurer que deux tables contiennent le même nombre de colonnes. Pour illustrer cela, nous allons créer une table nommée “article3” avec 2 colonnes supplémentaires nommées extra1 et extra2 :
Afin de comparer les colonnes, j’utiliserai cette requête :
La requête compare les différentes colonnes entre la table “article” et “article3”. Les différentes colonnes sont extra1 et extra2. Voici le résultat de la requête :
Comparer les tables SQL Server dans différentes bases de données
Maintenant, comparons les tables dans les bases de données dbtest01 et dbtest02 avec la logique UNION (apprenez la différence entre UNION et UNION ALL) et une sous-requête avec la logique NOT IN. La requête suivante affichera les tables différentes dans dbtest01 par rapport à dbtest02 :
Comparer les schémas en utilisant SSDT
SQL Server Data Tools permet de comparer les schémas de deux tables différentes à l’aide du projet de base de données SQL Server. Il peut générer des scripts qui vous permettront de synchroniser les données en quelques clics. Regardons cela de plus près.
1 – Dans le projet de base de données, accédez à l’Explorateur de solutions, faites un clic droit sur la base de données et sélectionnez l’option Comparer les schémas pour comparer les tables :
2 – Dans la sélection du schéma cible, appuyez sur le bouton Sélectionner la connexion pour sélectionner la table de destination à comparer avec la table source. Vous pourrez sélectionner une connexion existante ou en créer une nouvelle :
3 – Ensuite, appuyez sur le bouton Comparer et il affichera la différence. Il affichera les tables à ajouter ou les tables à supprimer :
4 – L’outil vous montrera l’intégralité du script T-SQL que vous pouvez appliquer ou modifier en fonction de vos besoins dans les tables :
Option de recherche SSIS Lookup
Cette option est très populaire dans les ETL (Extraction Transformation Loads). Le Lookup est une tâche de transformation SSIS qui permet de rechercher des données en utilisant des jointures à l’aide d’un jeu de données. Le Lookup vous permet de détecter les données modifiées entre 2 tables. Dans l’exemple suivant, je vais comparer les tables.
1 – Dans le projet SSIS, double-cliquez sur la tâche Flux de données pour la créer dans le flux de contrôle afin de créer une séquence Flux de données :
2 – Dans le Flux de données, faites glisser et déposez la source OLE DB avec la table source à comparer, la tâche Lookup et 2 tâches de destination OLE DB :
3 – Dans le Lookup, vous pouvez stocker entièrement les informations en cache ou les connecter partiellement. Nous avons également l’option sans cache. L’option de mode de cache complet stockera les données dans le cache SSIS. Le cache partiel stockera uniquement les valeurs en cache à mesure que chaque valeur distincte est trouvée dans le flux de données. Chaque valeur distincte sera obtenue à partir de la source spécifique :
4 – La tâche vous permettra de définir les clés à comparer et les colonnes de recherche avec l’alias de sortie utilisé :
Comme vous pouvez le voir, le Lookup permet de rechercher et de comparer des données. Vous pouvez ensuite voir quelles données ont été modifiées entre 2 tables.
Outils tiers
Il existe d’excellents outils pour comparer les tables, y compris les données et les schémas. Vous pouvez utiliser Visual Studio ou d’autres outils de comparaison SQL Server.
Prochaines étapes
Il existe plusieurs outils et méthodes pour comparer les données et les schémas. Choisissez la méthode qui convient le mieux à vos besoins en fonction des requêtes ci-dessus.
Pour plus d’informations, consultez ces liens :
Comparer les ensembles de données SQL Server avec INTERSECT et EXCEPT
Utiliser MERGE dans SQL Server pour insérer, mettre à jour et supprimer en même temps
Mise à jour de l’article : 21-10-2021
