Von: Daniel Calbimonte | Aktualisiert: 2019-05-22 | Kommentare (24) | Related: 1 | 2 | 3 | 4 | Mehr > Vergleich von Daten und Objekten
Problem
Manchmal müssen wir SQL Server-Tabellen und/oder -Daten vergleichen, um zu wissen, was geändert wurde. Dieser Tipp zeigt Ihnen verschiedene Möglichkeiten, Daten, Datentypen und Tabellen mit SQLServer zu vergleichen.
Lösung
Ich werde Ihnen verschiedene Methoden zeigen, um Änderungen zu identifizieren. Nehmen wir an, wir haben zwei ähnliche Tabellen in verschiedenen Datenbanken und wollen wissen, was sich unterscheidet.
Hier ist ein Skript, das Beispieldatenbanken, -tabellen und -daten erzeugt.
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
Der T-SQL-Code erzeugt 2 Tabellen in verschiedenen Datenbanken. Die Tabellennamen sind gleich, aber die Tabelle in der Datenbank dbtest02 enthält eine zusätzliche Zeile, wie unten gezeigt:
Lassen Sie uns schauen, wie wir diese Tabellen mit verschiedenen Methoden vergleichen können.
Vergleichen von SQL Server-Daten in Tabellen mit einem LEFT JOIN
Mit dem LEFT JOIN können wir Werte bestimmter Spalten vergleichen, die zwei Tabellen nicht gemeinsam haben.
Beispiel:
SELECT *FROM dbtest02.dbo.article d2LEFT JOIN dbtest01.dbo.article d1 ON d2.id = d1.id
Der LEFT JOIN zeigt alle Zeilen aus der linken Tabelle „dbtest02.dbo.article“ an, auch wenn es keine Übereinstimmungen in der Tabelle „dbtest01.dbo.article“ gibt:
In diesem Beispiel vergleichen wir 2 Tabellen und es werden die Werte von NULL angezeigt, wenn es keine übereinstimmenden Zeilen gibt. Diese Methode funktioniert, um neue Zeilen zu überprüfen, aber wenn wir andere Spalten aktualisieren, hilft der LEFT JOIN nicht.
Dies kann in beide Richtungen durchgeführt werden, um zu sehen, ob es in der anderen Richtung Unterschiede gibt. Diese Abfrage gibt nur die 3 übereinstimmenden Zeilen zurück.
SELECT *FROM dbtest01.dbo.article d1LEFT JOIN dbtest02.dbo.article d2 ON d1.id = d2.id
Vergleichen von SQL Server-Daten in Tabellen mit der EXCEPT-Klausel
Except zeigt den Unterschied zwischen zwei Tabellen an (die Oracle-Leute verwendeneminus statt except und die Syntax und Verwendung ist die gleiche). Sie wird verwendet, um die Unterschiede zwischen zwei Tabellen zu vergleichen. Sehen wir uns zum Beispiel die Unterschiede zwischen zwei Tabellen an:
Lassen Sie uns nun eine Abfrage mit except ausführen:
SELECT * FROM dbtest02.dbo.articleEXCEPTSELECT * FROM dbtest01.dbo.article
Das except liefert die Differenz zwischen den Tabellen von dbtest02 und dbtest01:
Wenn wir die Tabellen in der Abfrage umdrehen, werden wir keine Datensätze sehen, weil die Tabelle in der Datenbank dbtest02 alle Datensätze plus einen zusätzlichen enthält.
SELECT * FROM dbtest01.dbo.articleEXCEPTSELECT * FROM dbtest02.dbo.article
Diese Methode ist besser als die erste, denn wenn wir Werte für andereSpalten wie Typ und Kosten ändern, wird EXCEPT den Unterschied bemerken.
Hier ist ein Beispiel, wenn wir die ID „001“ in der Datenbank dbtest01 aktualisieren und die Kosten von „40“ auf „1“ ändern. Wenn wir die Datensätze aktualisieren und dann die Abfrage erneut ausführen, sehen wir jetzt folgende Unterschiede:
Es wird leider kein Skript zum Synchronisieren der Tabellen erstellt.
Vergleichen von SQL Server-Daten in Tabellen mit dem Tablediff-Tool
Es gibt ein nettes Kommandozeilen-Tool zum Vergleichen von Tabellen. Dieses befindet sich im Ordner „C:\Programme\Microsoft SQL Server\110\COM\“. Dieses Befehlszeilentool wird für den Vergleich von Tabellen verwendet. Es generiert auch ein Skript mit den INSERT-, UPDATE- undDELETE -Anweisungen, um die Tabellen zu synchronisieren. Weitere Details finden Sie in diesem Artikel.
Vergleichen von SQL Server-Daten in Tabellen mit Change Data Capture (CDC)
Diese Funktion ist in SQL Server 2008 und höher verfügbar. Sie müssen diese Funktion aktivieren und außerdem muss der SQL Server Agent laufen. Grundsätzlich werden Systemtabellen erstellt, die die Änderungen in den Tabellen verfolgen, die Sie überwachen möchten. Es vergleicht keine Tabellen, aber es verfolgt die Änderungen in den Tabellen.
Weitere Informationen finden Sie in diesen Tipps zur Änderungsdatenerfassung (CDC).
SQL Server-Datentypen zwischen zwei Tabellen vergleichen
Was passiert, wenn wir die Datentypen vergleichen wollen? Gibt es eine Möglichkeit, die Datentypen zu vergleichen?
Ja, wir können die . system views verwenden, um die Informationen zu überprüfen und zu vergleichen. Wir erstellen eine neue Tabelle mit dem Namen dbo.article2 mit einer Spalte, die einen anderen Datentyp hat als die Tabelle 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
Der Unterschied besteht darin, dass die id jetzt ein int ist und nicht mehr einnchar(10) wie in den anderen Tabellen.
Die Abfrage, um die Datentypen der Tabellen article und article1 zu vergleichen, wäre:
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
Die Ergebnisse sind wie folgt:
Die Abfrage vergleicht die Datentypen dieser beiden Tabellen. Wir vergleichen die Tabelle „Artikel“ mit der Tabelle „Artikel2“ und zeigen, ob sich die Datentypen unterscheiden.
Vergleichen, ob es zusätzliche Spalten zwischen SQL Server-Tabellen gibt
Manchmal müssen wir sicherstellen, dass zwei Tabellen die gleiche Anzahl von Spalten enthalten.Um dies zu veranschaulichen, werden wir eine Tabelle mit dem Namen „article3“ mit 2 Extra-Spalten namens extra1 und extra2 erstellen:
USE dbtest01GOCREATE TABLE .( NOT NULL, nchar(10) NULL, nchar(10) NULL, extra1 int, extra2 int)
Um die Spalten zu vergleichen, werde ich diese Abfrage verwenden:
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' )
Die Abfrage vergleicht die verschiedenen Spalten der Tabelle „artikel“ und „artikel3“.Die verschiedenen Spalten sind extra1 und extra2. Dies ist das Ergebnis der Abfrage:
Vergleich der SQL Server-Tabellen in verschiedenen Datenbanken
Nun wollen wir die Tabellen in den Datenbanken dbtest01 und dbtest02 vergleichen. Die folgende Abfrage zeigt die verschiedenen Tabellen in dbtest01 im Vergleich zu 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 )
Tools von Drittanbietern
Es gibt tolle Tools, um Tabellen inklusive Daten und Schemata zu vergleichen. Sie könnenVisualStudio verwenden oder andereSQL-Server-Vergleichstools einsetzen.
Nächste Schritte
- Es gibt mehrere Tools und Möglichkeiten, Daten und Schemas zu vergleichen. Wählen Sie die Methode, die für Ihre Bedürfnisse am besten geeignet ist.
- Weitere Informationen finden Sie unter diesen Links:
- SQL Server Except
- SQL Server Left Join
- SQL Server Change Data Capture
- SQL Server Tablediff Utility
Last Updated: 2019-05-22
Über den Autor
Daniel Calbimonte ist ein Microsoft SQL Server MVP, Microsoft Certified Trainer und Microsoft Certified IT Professional.Alle meine Tipps ansehen