Par : Daniel Calbimonte | Mis à jour : 2019-05-22 | Commentaires (24) | Associé : 1 | 2 | 3 | 4 | Plus > Comparaison de données et d’objets
Problème
Parfois, nous avons besoin de comparer des tables et/ou des données SQL Server pour savoir ce qui a été modifié. Cette astuce vous montre différentes façons de comparer des données, des types de données et des tables en utilisant SQLServer.
Solution
Je vais vous montrer différentes méthodes pour identifier les changements. Disons que nous avonsdeux 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 échantillons.
CREATE DATABASE dbtest01GOUSE dbtest01GOCREATE TABLE . ( (10) NOT NULL, (10) NULL, (10) NULL, CONSTRAINT PRIMARY KEY CLUSTERED ( ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON ) ON GOINSERT INTO . VALUES ('001', '1', '40'), ('002', '2', '80'), ('003', '3', '120')GOCREATE DATABASE dbtest02GOUSE dbtest02GOCREATE TABLE . ( (10) NOT NULL, (10) NULL, (10) NULL, CONSTRAINT PRIMARY KEY CLUSTERED ( ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON ) ON GOINSERT INTO . VALUES ('001', '1', '40'), ('002', '2', '80'), ('003', '3', '120'), ('004', '4', '160')GO
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 de 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 à l’aide d’un LEFT JOIN
Avec le LEFT JOIN, nous pouvons comparer les valeurs de colonnes spécifiques qui ne sont pas communes entre deux tables.
Par exemple :
SELECT *FROM dbtest02.dbo.article d2LEFT JOIN dbtest01.dbo.article d1 ON d2.id = d1.id
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 valeurs de 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 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 requête renverra juste les 3 lignes correspondantes.
SELECT *FROM dbtest01.dbo.article d1LEFT JOIN dbtest02.dbo.article d2 ON d1.id = d2.id
Comparer les données SQL Server dans les tables en utilisant la clause EXCEPT
Except montre la différence entre deux tables (les gars d’Oracle utilisenteminus 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:
Maintenant, exécutons une requête en utilisant except:
SELECT * FROM dbtest02.dbo.articleEXCEPTSELECT * FROM dbtest01.dbo.article
L’except renvoie la différence entre les tables de dbtest02 et dbtest01 :
Si nous inversons les tables dans la requête, nous ne verrons aucun enregistrement, car la table de la base de données dbtest02 possède tous les enregistrements plus un supplémentaire.
SELECT * FROM dbtest01.dbo.articleEXCEPTSELECT * FROM dbtest02.dbo.article
Cette méthode est meilleure que la première, car si nous changeons les valeurs des autrescolonnes comme le type et le coût, EXCEPT remarquera la différence.
Voici unexemple 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 ces différences maintenant :
Malheureusement, il ne crée pas de script pour synchroniser les tables.
Comparer les données du serveur SQL dans les tables à l’aide de l’outil Tablediff
Il existe un bel outil en ligne de commande utilisé pour comparer les tables. Il se trouve dans le dossier « C:\Program Files\Microsoft SQL Server\110\COM\ ». Cet outil de ligne de commande est utilisé pour comparer les tables. Il génère également un script contenant les instructions INSERT, UPDATE etDELETE pour synchroniser les tables. Pour plus de détails, reportez-vous à cet article dediffusion.
Comparer les données SQL Server dans les tables à l’aide de Change Data Capture (CDC)
Cette fonctionnalité est disponible à partir de SQL Server 2008. Vous devez activer cette fonctionnalité etvous devez également avoir l’agent SQL Server en cours d’exécution. Fondamentalement, il crée des tableaux système qui suivent les changements dans vos tables que vous voulez surveiller. Il ne compare pas les tables, mais il suit les changements dans les tables.
Pour plus d’informations, reportez-vous à ces conseils sur la capture des données de changement (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 ? Y a-t-il un moyen de comparer les types de données ?
Oui, nous pouvons utiliser les vues système . pour vérifier et comparer les informations. Nous allons créer une nouvelle table nommée dbo.article2 avec une colonne dont le type de données est différent de celui de la table dbo.article :
USE dbtest01GOCREATE TABLE .( NOT NULL, nchar(10) NULL, nchar(10) NULL, CONSTRAINT PRIMARY KEY CLUSTERED ( ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON ) ON GO
La différence est que l’id est maintenant un int au lieu denchar(10) comme les autrestables.
La requête pour comparer les types de données des tables article et article1 serait:
USE dbtest01GOSELECT c1.table_name, c1.COLUMN_NAME, c1.DATA_TYPE, c2.table_name, c2.DATA_TYPE, c2.COLUMN_NAMEFROM . c1LEFT JOIN . c2 ON c1.COLUMN_NAME = c2.COLUMN_NAMEWHERE c1.TABLE_NAME = 'article' AND c2.TABLE_NAME = 'article2' AND c1.data_type <> c2.DATA_TYPE
Les résultats sont les suivants:
La requête compare les types de données de ces deux tables. Toutes les informationsdes colonnes peuvent être obtenues à partir de la vue . system.Nous comparons la table « article » avec la table « article2 » et montrons si l’un des types de données est différent.
Comparer s’il y a des colonnes supplémentaires entre les tables 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 extracolonnes nommées extra1 et extra2:
USE dbtest01GOCREATE TABLE .( NOT NULL, nchar(10) NULL, nchar(10) NULL, extra1 int, extra2 int)
Afin de comparer les colonnes je vais utiliser cette requête :
USE dbtest01GOSELECT c2.table_name, c2.COLUMN_NAMEFROM . c2WHERE table_name = 'article3' AND c2.COLUMN_NAME NOT IN ( SELECT column_name FROM . WHERE table_name = 'article' )
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
Comparons maintenant les tables dans la base de données dbtest01 et dbtest02. La requête suivante montrera les différentes tables dans dbtest01 par rapport à dbtest02:
SELECT 'dbtest01' AS dbname, t1.table_nameFROM dbtest01.. t1WHERE table_name NOT IN ( SELECT t2.table_name FROM dbtest02.. t2 )UNIONSELECT 'dbtest02' AS dbname, t1.table_nameFROM dbtest02.. t1WHERE table_name NOT IN ( SELECT t2.table_name FROM dbtest01.. t2 )
Third Party Tools
Il existe d’excellents outils pour comparer les tables, y compris les données et les schémas. Vous pouvez utiliserVisualStudio ou utiliser d’autres outils de comparaison de serveurs SQL.
Prochaines étapes
- Il existe de multiples outils et moyens de comparer des données et des schémas. Sélectionnez la méthode qui fonctionne le mieux pour vos besoins.
- Pour plus d’informations, reportez-vous à ces liens:
- SQL Server Except
- SQL Server Left Join
- SQL Server Change Data Capture
- SQL Server Tablediff Utility
Dernière mise à jour : 2019-05-22
.
À propos de l’auteur
Daniel Calbimonte est un MVP Microsoft SQL Server, formateur certifié Microsoft et professionnel de l’informatique certifié Microsoft.Voir tous mes conseils