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
Nous allons couvrir différentes méthodes pour identifier les changements en utilisant diverses requêtes SQL ainsi que quelques outils de développement.
Supposons que nous ayons deux tables similaires dans différentes bases de données et que nous voulions 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 différentes bases de données. 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 :
[Insérer l’image ici]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 LEFT
Avec une jointure LEFT, nous pouvons comparer les valeurs de colonnes spécifiques qui ne sont pas communes entre deux tables.
Par exemple, avec cette instruction SELECT :
[Insérer l’image ici]L’ensemble de résultats de la jointure LEFT affiche toutes les lignes de la table de gauche “dbtest02.dbo.article”, même s’il n’y a aucune correspondance dans la table “dbtest01.dbo.article” :
[Insérer l’image ici]Dans cet exemple, nous comparons 2 tables et les NULL sont affichés s’il n’y a aucune ligne correspondante. Cette méthode fonctionne pour vérifier les nouvelles lignes, mais si nous mettons à jour d’autres colonnes, la jointure LEFT n’est 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 retournera 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 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 :
[Insérer l’image ici]Exécutons maintenant une requête en utilisant except :
[Insérer l’image ici]La clause except renvoie la différence entre les tables de dbtest02 et dbtest01 :
[Insérer l’image ici]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 des autres colonnes comme le type et le coût, EXCEPT remarquera la différence.
Voici un exemple si nous mettons à jour l’ID “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 :
[Insérer l’image ici]Malheureusement, cela ne crée pas un 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 le Change Data Capture (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 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 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 :
[Insérer l’image ici]La différence est que l’ID est maintenant un 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 article2 serait :
[Insérer l’image ici]Les résultats sont les suivants :
[Insérer l’image ici]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 l’un des types de données est différent.
Vérifier s’il y a des colonnes supplémentaires entre les tables de la 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 :
[Insérer l’image ici]Afin de comparer les colonnes, j’utiliserai cette requête :
[Insérer l’image ici]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 :
[Insérer l’image ici]Comparer les tables SQL Server dans différentes bases de données
Maintenant, comparons 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 affiche les tables différentes dans dbtest01 par rapport à dbtest02 :
[Insérer l’image ici]Comparer les schémas à l’aide de 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. 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 :
[Insérer l’image ici]2 – Dans la sélection du schéma cible, appuyez sur le bouton Sélectionner la 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 :
[Insérer l’image ici]3 – Ensuite, appuyez sur le bouton Comparer et il affichera la différence. Il affichera les tables à ajouter ou les tables à supprimer :
[Insérer l’image ici]4 – L’outil vous montrera l’ensemble du script T-SQL que vous pouvez appliquer ou modifier selon vos besoins dans les tables :
[Insérer l’image ici]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 le créer dans le Flux de contrôle afin de créer une séquence de flux de données :
[Insérer l’image ici]2 – Dans le Flux de données, faites glisser et déposer la source OLE DB avec la table source à comparer, la tâche de recherche et 2 destinations OLE DB :
[Insérer l’image ici]3 – Dans la recherche, vous pouvez stocker pleinement les informations dans le cache ou vous connecter partiellement. Nous avons également l’option de non mise en cache. L’option de mode de mise en cache complète stocke les données dans le cache SSIS. La mise en cache partielle ne stocke que 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 :
[Insérer l’image ici]4 – La tâche vous permet de définir les clés à comparer et les colonnes de recherche avec l’alias de sortie utilisé :
[Insérer l’image ici]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 permettant de comparer des 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. Sélectionnez 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 jeux de données SQL Server avec INTERSECT et EXCEPT
- Jointure LEFT SQL Server
- Capture de données de modification 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
