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 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 des bases de données différentes et que nous voulions savoir ce qui est différent. Voici un script qui crée des bases de données d’échantillon, des tables et des données.
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.
Examinons les méthodes que nous pouvons utiliser pour comparer ces tables en utilisant différentes méthodes.
Comparer les données de SQL Server dans les tables en utilisant une jointure LEFT JOIN
Avec une jointure LEFT JOIN, nous pouvons comparer les valeurs de colonnes spécifiques qui ne sont pas communes entre deux tables.
Par exemple, avec cette instruction SELECT :
SELECT dbtest02.dbo.article.id, dbtest02.dbo.article.title, dbtest02.dbo.article.category
FROM dbtest02.dbo.article
LEFT JOIN dbtest01.dbo.article ON dbtest02.dbo.article.id = dbtest01.dbo.article.idL’ensemble de résultats de la jointure LEFT JOIN 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”.
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 LEFT JOIN 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
La clause EXCEPT montre 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 :
SELECT id, title, category
FROM dbtest01.dbo.article
EXCEPT
SELECT id, title, category
FROM dbtest02.dbo.articleMaintenant exécutons une requête en utilisant EXCEPT :
SELECT id, title, category
FROM dbtest02.dbo.article
EXCEPT
SELECT id, title, category
FROM dbtest01.dbo.articleLa requête 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 dans la base de données “dbtest02” contient tous les enregistrements plus un enregistrement 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’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.
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 existe un outil gratuit en ligne de commande utilisé pour comparer des tables. Il se trouve dans le dossier “C:Program FilesMicrosoft SQL Server110COM”. Cet outil en ligne de commande est utilisé pour comparer des 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 la capture de données modifiées (CDC)
Cette fonctionnalité est disponible dans SQL Server 2008 et ultérieur. Vous devez activer cette fonctionnalité et vous devez également avoir l’Agent SQL Server en cours d’exécution. Fondamentalement, cela 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 la capture de données modifiées (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 appelée “dbo.article2” avec une colonne de type de données différent de la table “dbo.article” :
CREATE TABLE dbo.article2 (
id INT,
title NVARCHAR(100),
category NVARCHAR(50)
)La différence est que l’ID est maintenant un “int” au lieu de “nchar(10)” comme les autres tables.
La requête pour comparer les types de données des tables “article” et “article2” serait :
SELECT article.COLUMN_NAME, article2.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS AS article
JOIN INFORMATION_SCHEMA.COLUMNS AS article2 ON article.COLUMN_NAME = article2.COLUMN_NAME
WHERE article.TABLE_NAME = 'article'
AND article2.TABLE_NAME = 'article2'
AND article.DATA_TYPE <> article2.DATA_TYPELes 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 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 appelées “extra1” et “extra2” :
CREATE TABLE dbo.article3 (
id INT,
title NVARCHAR(100),
category NVARCHAR(50),
extra1 INT,
extra2 INT
)Afin de comparer les colonnes, j’utiliserai cette requête :
SELECT column_name
FROM information_schema.columns
WHERE table_name = 'article'
MINUS
SELECT column_name
FROM information_schema.columns
WHERE table_name = 'article3'La requête compare les colonnes différentes 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
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” comparées à “dbtest02” :
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'dbo'
AND table_name NOT IN (
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'dbtest02'
)
UNION
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'dbtest02'
AND table_name NOT IN (
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'dbo'
)Comparer les schémas à l’aide de SSDT (SQL Server Data Tools)
Les outils SQL Server Data Tools 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 en quelques clics. Jetons-y un coup d’œil.
- Dans le projet de base de données, accédez à l’Explorateur de solutions et cliquez avec le bouton droit sur la base de données, puis sélectionnez l’option “Comparer les schémas” pour comparer les tables.
- Dans la sélection du schéma cible, cliquez sur “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.
- Ensuite, cliquez sur le bouton “Comparer” et il affichera les différences. Il montrera les tables à ajouter ou les tables à supprimer.
- 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 (Integration Services)
Cette option est très populaire dans les ETL (Extraction Transformation Load). La recherche est une tâche de transformation SSIS qui permet de rechercher des données à l’aide de jointures à l’aide d’un ensemble de données. La recherche vous permet de détecter les données modifiées entre 2 tables. Dans l’exemple suivant, je vais comparer les tables.
- 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 de flux de données.
- Dans le flux de données, faites glisser et déposez la source OLE DB avec la table source à comparer, la tâche de recherche et 2 destinations OLE DB.
- Dans la recherche, vous pouvez stocker entièrement les informations en cache ou vous connecter partiellement. Nous avons également l’option “no cache”. Le mode de cache complet stockera les données dans le cache SSIS. Le cache partiel ne stockera en cache que les valeurs à 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.
- La tâche vous permettra de définir les clés de comparaison et les colonnes de recherche avec l’alias de sortie utilisé.
Comme vous pouvez le voir, la recherche permet de rechercher et de comparer des données. Vous pouvez ensuite obtenir quelles données 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 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 LEFT de 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
À propos de l’auteur :
Daniel Calbimonte est un expert en bases de données avec une vaste expérience dans l’utilisation de SQL Server. Il partage ses connaissances et ses astuces à travers ses articles informatifs. Dernière mise à jour de l’article : 2021-10-21.
