Lorsque vous travaillez avec SQL Server, il est souvent nécessaire de comparer les tables et les données pour identifier les différences ou les changements. Cela peut être utile dans différents scénarios, tels que la vérification de l’intégrité des données, l’identification des écarts entre les bases de données ou le suivi des changements au fil du temps. Dans cet article, nous explorerons différentes méthodes et outils pouvant être utilisés pour comparer les tables et les données SQL Server.
Comparaison des données dans les tables à l’aide d’une jointure LEFT JOIN
Une façon de comparer les données dans les tables SQL Server est d’utiliser une jointure LEFT JOIN. Cela nous permet de comparer des colonnes spécifiques qui ne sont pas communes entre deux tables. Par exemple:
SELECT *
FROM dbtest02.dbo.article d2
LEFT JOIN dbtest01.dbo.article d1 ON d2.id = d1.id
L’ensemble de résultats de la jointure LEFT JOIN affichera toutes les lignes de la table de gauche, même s’il n’y a pas de correspondance dans la table de droite. Cette méthode est utile pour vérifier les nouvelles lignes, mais elle peut ne pas capturer les changements dans les autres colonnes.
Utilisation de la clause EXCEPT pour comparer les données
La clause EXCEPT peut être utilisée pour comparer les différences entre deux tables. Elle renvoie les lignes qui existent dans la première table mais pas dans la deuxième table. Par exemple:
SELECT * FROM dbtest02.dbo.article
EXCEPT
SELECT * FROM dbtest01.dbo.article
Cette requête renverra les différences 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 dbtest02 contient tous les enregistrements plus un supplémentaire. La clause EXCEPT est une meilleure option pour capturer les changements dans les autres colonnes.
Utilisation de l’outil Tablediff
L’outil Tablediff est un outil en ligne de commande fourni par SQL Server qui peut être utilisé pour comparer les tables. Il génère un script avec des instructions INSERT, UPDATE et DELETE pour synchroniser les tables. L’outil se trouve dans le dossier “C:\Program Files\Microsoft SQL Server\110\COM\”. C’est un outil utile pour comparer les tables et générer des scripts de synchronisation.
Utilisation de Change Data Capture (CDC)
Change Data Capture (CDC) est une fonctionnalité disponible dans SQL Server 2008 et les versions ultérieures. Elle suit les modifications dans les tables en créant des tables système qui capturent les modifications. Bien que CDC ne compare pas directement les tables, il fournit un mécanisme pour surveiller et suivre les modifications dans les tables. L’activation de CDC et l’exécution de SQL Server Agent vous permettent de suivre les modifications dans vos tables.
Comparaison des types de données entre les tables
Si vous devez comparer les types de données des colonnes dans deux tables, vous pouvez utiliser les vues système [INFORMATION_SCHEMA].[COLUMNS]. Ces vues fournissent des informations sur les colonnes d’une base de données. Par exemple:
SELECT
c1.table_name,
c1.COLUMN_NAME,
c1.DATA_TYPE,
c2.table_name,
c2.DATA_TYPE,
c2.COLUMN_NAME
FROM [INFORMATION_SCHEMA].[COLUMNS] c1
LEFT JOIN [INFORMATION_SCHEMA].[COLUMNS] c2 ON c1.COLUMN_NAME = c2.COLUMN_NAME
WHERE c1.TABLE_NAME = 'article'
AND c2.TABLE_NAME = 'article2'
AND c1.data_type <> c2.DATA_TYPE
Cette requête compare les types de données de la table “article” avec la table “article2”. Elle récupère des informations à partir de la vue système [INFORMATION_SCHEMA].[COLUMNS] et identifie les différences de types de données.
Comparaison du nombre de colonnes entre les tables
Parfois, il est nécessaire de s’assurer que deux tables ont le même nombre de colonnes. Pour comparer les colonnes, vous pouvez utiliser une requête comme celle-ci:
SELECT
c2.table_name,
c2.COLUMN_NAME
FROM [INFORMATION_SCHEMA].[COLUMNS] c2
WHERE table_name = 'article3'
AND c2.COLUMN_NAME NOT IN
( SELECT column_name
FROM [INFORMATION_SCHEMA].[COLUMNS]
WHERE table_name = 'article'
)
Cette requête compare les colonnes entre les tables “article” et “article3”. Elle identifie les colonnes présentes dans “article3” mais qui n’existent pas dans “article”. Cela peut être utile pour garantir la cohérence du nombre de colonnes entre les tables.
Comparaison des tables dans différentes bases de données
Pour comparer les tables dans différentes bases de données, vous pouvez utiliser la logique UNION et une sous-requête avec la logique NOT IN. Par exemple:
SELECT 'dbtest01' AS dbname, t1.table_name
FROM dbtest01.[INFORMATION_SCHEMA].[tables] t1
WHERE table_name NOT IN
( SELECT t2.table_name
FROM dbtest02.[INFORMATION_SCHEMA].[tables] t2
)
UNION
SELECT 'dbtest02' AS dbname, t1.table_name
FROM dbtest02.[INFORMATION_SCHEMA].[tables] t1
WHERE table_name NOT IN
( SELECT t2.table_name
FROM dbtest01.[INFORMATION_SCHEMA].[tables] t2
)
Cette requête compare les tables dans les bases de données dbtest01 et dbtest02. Elle identifie les tables présentes dans une base de données mais pas dans l’autre. Cela peut être utile pour comparer les schémas entre les bases de données.
Utilisation de SSDT pour comparer les schémas
SQL Server Data Tools (SSDT) vous 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 synchronisent les données en quelques clics seulement. Voici comment vous pouvez utiliser SSDT pour comparer les schémas:
- Dans le projet de base de données, accédez à l’Explorateur de solutions et cliquez avec le bouton droit de la souris sur la base de données. Sélectionnez l’option Comparer les schémas pour comparer les tables.
- Dans le schéma cible sélectionné, choisissez la destination de la table à comparer avec la table source. Vous pouvez sélectionner une connexion existante ou en créer une nouvelle.
- Cliquez sur le bouton de comparaison pour voir les différences. L’outil affichera les tables à ajouter ou à supprimer.
- L’outil fournira un script T-SQL que vous pouvez appliquer ou modifier selon vos besoins.
SSDT est un outil puissant pour comparer et synchroniser les schémas entre les tables.
Utilisation de l’option de recherche SSIS
La tâche de transformation de recherche SSIS est couramment utilisée dans les processus ETL (Extraction, Transformation et Chargement). Elle vous permet de rechercher et de comparer des données à l’aide de jointures avec un ensemble de données. Cela peut être utile pour détecter les données modifiées entre deux tables. Voici un exemple:
- Dans le projet SSIS, créez une tâche de flux de données dans le flux de contrôle pour créer une séquence de flux de données.
- Dans le flux de données, ajoutez une source OLE DB avec la table source à comparer, une tâche de recherche et deux destinations OLE DB.
- Dans la tâche de recherche, vous pouvez choisir de stocker entièrement les informations en cache ou de vous connecter partiellement. Vous pouvez également sélectionner l’option sans cache.
- Définissez les clés de comparaison et les colonnes de recherche avec l’alias de sortie utilisé.
La transformation de recherche vous permet de rechercher et de comparer des données, ce qui vous permet d’identifier les changements entre deux tables.
Outils tiers
Il existe également des outils tiers disponibles qui peuvent comparer les tables, y compris les données et les schémas. Ces outils offrent des fonctionnalités supplémentaires pour comparer et synchroniser les tables SQL Server. Certaines options populaires incluent Visual Studio et d’autres outils de comparaison SQL Server.
En conclusion, il existe différentes méthodes et outils disponibles pour comparer les tables et les données SQL Server. Que vous ayez besoin de comparer des données, des types de données, des schémas ou le nombre de colonnes, il existe une solution adaptée à vos besoins spécifiques. En exploitant ces techniques, vous pouvez garantir l’intégrité des données, suivre les changements et maintenir la cohérence dans vos bases de données SQL Server.
Article mis à jour pour la dernière fois le 2021-10-21