Problème
Parfois, nous devons 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 diverses requêtes SQL ainsi que quelques outils de développement.
Supposons que nous avons deux tables similaires dans des bases de données différentes et que nous voulons savoir ce qui est différent. 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 de table 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 de 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 :
Le résultat de la jointure gauche montre 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 valeurs NULL sont affichées 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 nous aide pas.
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 de SQL Server dans les tables en utilisant la clause EXCEPT
Except affiche la différence entre deux tables (les gars d’Oracle DBMS utilisent “minus” à la place 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 :
Maintenant, exécutons une requête en utilisant “except” :
Except 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 de 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 maintenant ces différences :
Malheureusement, elle ne crée pas de script pour synchroniser les tables.
Comparer les données de SQL Server dans les tables en utilisant l’outil Tablediff
Il existe un outil en ligne de commande gratuit utilisé pour comparer les tables. Il peut être trouvé 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 de SQL Server dans les tables en utilisant la capture de modifications (CDC)
Cette fonctionnalité est disponible dans SQL Server 2008 et ultérieur. Vous devez activer cette fonctionnalité et vous devez également avoir SQL Server Agent en cours d’exécution. Fondamentalement, elle crée des tables système qui suivent les modifications dans les tables que vous souhaitez surveiller. Elle ne compare pas les tables, mais suit les modifications dans les tables.
Pour plus d’informations, consultez ces astuces sur la Capture des modifications (CDC).
Comparer les types de données de 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 de 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 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 montrons si certains des types de données sont différents.
Comparer s’il existe des colonnes supplémentaires entre les tables de base 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 colonnes différentes sont “extra1” et “extra2”. Voici le résultat de la requête :
Comparer les tables SQL Server dans différentes bases de données
Comparons maintenant les tables dans la base 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 montrera les différentes tables dans “dbtest01” par rapport à “dbtest02” :
Comparer les schémas en utilisant SSDT
Les outils de développement SQL Server permettent de comparer les schémas de deux tables différentes à l’aide du projet de base de données SQL Server. Ils peuvent générer des scripts qui vous permettront de synchroniser les données avec quelques clics. Jetons-y un coup d’œil.
1 – Dans le projet de base de données, allez dans 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 le schéma cible sélectionné, appuyez sur le bouton “Sélectionner une connexion” pour sélectionner la destination de la table à 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 montrera les tables à ajouter ou les tables à supprimer :
4 – L’outil vous montrera l’intégralité du script T-SQL que vous pourrez appliquer ou modifier selon vos besoins dans les tables :
Option de recherche SSIS
Cette option est très populaire dans les ETL (Extraction Transformation Loads). La recherche 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. La recherche 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 Recherche et 2 destinations OLE DB :
3 – Dans la Recherche, vous pouvez stocker entièrement les informations dans le cache ou vous 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 de cache au fur et à 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 permet de définir les clés de comparaison et les colonnes de recherche avec les alias de sortie utilisés :
Comme vous pouvez le voir, la recherche permet de rechercher et de comparer des données. Vous pouvez ensuite obtenir les données qui ont été modifiées entre 2 tables.
Outils tiers
Il existe de nombreux 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 de SQL Server.
Conclusion
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 les liens suivants :
- Comparer les ensembles de données SQL Server avec INTERSECT et EXCEPT
- Jointure gauche SQL Server
- Capture de données modifiées SQL Server
- Utilitaire Tablediff SQL Server
- Utilisation de MERGE dans SQL Server pour insérer, mettre à jour et supprimer en même temps
- Exemple de curseur SQL Server
