Redgate Hub
Einführung
Es gibt mehrere Gründe, warum Sie Tabellen oder Ergebnisse vergleichen müssen.
- Manchmal muss man nur wissen, ob die Tabellen Daten enthalten, die gleich oder verschieden sind; Keine Details: nur ja oder nein. Dies ist typisch für Testassertionen, bei denen Sie nur wissen müssen, ob Ihre Routine oder Ihr Batch ein Ergebnis mit den richtigen Daten liefert. wenn mit bestimmten Werten für die Parameter versehen. Es ist entweder falsch oder richtig
- Gelegentlich müssen Sie wissen, welche Zeilen sich geändert haben, ohne möglicherweise genau zu wissen, welche Spalten sich wie geändert haben.
- Es gibt Zeiten, in denen Sie eine große Tabelle in Bezug auf Spalten und Zeilen haben und etwas benötigen, das Ihnen speziell die Spalten anzeigt, die ihren Wert geändert haben. Möglicherweise möchten Sie dies auch, wenn Sie einen Fehler in einer Routine aufspüren, bei der Sie andernfalls Zeit damit verschwenden müssen, ‚mit dem Auge‘ zu scannen.
Wir werden diese drei ziemlich unterschiedlichen Aufgaben in SQL angehen
Wenn zwei Tabellen eine unterschiedliche Anzahl von Zeilen haben, können sie natürlich nicht gleich sein. Es gibt jedoch Zeiten, in denen Sie wissen müssen, ob Table_B alle Zeilen von Table_A ohne Unterschiede enthält. Wenn Sie mehr Details wünschen, möchten Sie vielleicht sogar die Zeilen in beiden Tabellen kennen, die nicht gemeinsam sind, oder die gemeinsamen Zeilen, wie durch den Primärschlüssel angegeben, die unterschiedlich waren. Warum nur zwei Tabellen vergleichen? Es gibt Möglichkeiten, so viele zu vergleichen, wie Sie benötigen. (wie zum Beispiel, wenn Sie die Metadaten in mehreren Datenbank-Snapshots vergleichen). Ja, es gibt viele Variationen
Sie haben Werkzeuge und Funktionen, um dieses Zeug zu tun, sicher?
Es gibt immer einen Platz für Tools wie SQL Data Compare, TableDiff, tSQLt oder Change Data Capture. Viel hängt von den Umständen und der Art der Aufgabe ab. Das Problem der Durchführung von Audits bei Änderungen an Daten in einem Live-System ist ein separates Thema, ebenso wie die Synchronisation von Tabellen und Datenbanken. Der Vergleich von XML-Dokumenten ist ebenfalls nicht möglich. Wir werden uns nur mit dem routinemäßigen Vergleich der Daten in Tabellen befassen
Ich verwende höchstwahrscheinlich TSQL-Techniken, um Tabellen zu vergleichen, wenn:
Entwickeln …
Im Zuge der Entwicklung einer Datenbank werden viele Tabellen verglichen. Es ist nicht nur das große Zeug: Jede tabellenwertige Funktion benötigt zum Beispiel einen Testbaum im Build-Skript, der sicherstellt, dass sie unter allen denkbaren Testumständen das tut, was Sie denken, und alle bösen Randfälle einbezieht, in denen es von den Testern in der Vergangenheit gefangen wurde. Jede gespeicherte Prozedur benötigt einen Test, um sicherzustellen, dass der Prozess, den sie ausführt, genau das tut, was beabsichtigt ist, und sonst nichts.
Es gab eine Zeit, in der die Build-Aktivität eher gemächlich war, aber wenn Sie einen nächtlichen Build- und Integrationstest haben, ist es am besten, ihn vollständig zu automatisieren und die lästige Arbeit loszuwerden.
ETL
Wenn Sie das Laden von Daten in ein System automatisieren, müssen Sie häufig verschiedene Bedingungen testen. Müssen Sie vorhandene Versionen der Zeilen aktualisieren und die neuen einfügen? Benötigen Sie eine Falle, um doppelte Einträge zu verhindern oder sogar vorhandene Einträge zu löschen?
Einrichten der Testdaten.
Die Skripte in diesem Artikel verwenden alle eine Tabelle aus der ehrwürdigen PUBS-Datenbank. Wir werden die Autoren-Tabelle verwenden, aber die Anzahl der Zeilen ein wenig auf 5000 erhöhen, um eine etwas realistischere Größe zu erhalten. Ich habe die Quelle für die Tabelle mit dem Artikel angegeben.
I then created a copy of the table …
|
1
2
3
4
5
6
|
SELECT * INTO authorsCopy
FROM authors
GO
ALTER TABLE dbo.authorsCopy ADD CONSTRAINT PK_authorsCopy PRIMARY KEY CLUSTERED
(au_id) ON PRIMARY
GO
|
Und dann einige der Zeilen geändert.
|
1
2
3
4
5 |
UPDATE authorsCopy SET address=“(Adresse,1,1,“)
WO au_ID IN (
WÄHLEN SIE TOP 10 au_id
VON authorsCopy f
Telefonisch BESTELLEN)
|
Jetzt sollten die beiden Tabellen mit ein paar kleinen Änderungen im Adressfeld überwiegend gleich sein
. um zu sehen, ob Tabellen unterschiedlich sind.
Manchmal möchte man nur wissen, ob Tabellen gleich sind. Ein Beispiel hierfür wäre die Überprüfung, ob eine TVF ordnungsgemäß funktioniert, indem ihr Ergebnis mit dem einer vorhandenen Tabelle mit den korrekten Ergebnissen verglichen wird. Der übliche Weg, dies zu tun, ist mit der CHECKSUM()Gruppe von Funktionen in SQL Server, weil sie sehr schnell sind.
Mit Prüfsummen
Sie können die BINARY_CHECKSUM Funktion verwenden, um zu überprüfen, ob Tabellen gleich sind: Nun, ungefähr gleich. Es ist schnell, aber es ist nicht perfekt, wie ich gleich demonstrieren werde. Wenn Sie eine Reihe von Tests haben, zum Beispiel ist es in der Regel ausreichend.
|
1
2
3
4
5 6
7
8
9
|
IF (
SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*))
VON den Autoren)=(
SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*))
VON authorsCopy)
WÄHLEN SIE ’sie sind wahrscheinlich gleich‘
SONST
WÄHLEN SIE ’sie sind anders‘
|
Damit dies funktioniert, darf Ihre Tabelle keinen TEXT, NTEXT, IMAGE or CURSOR (oder einen SQL_VARIANT mit einem dieser Typen) als Basistyp haben. Heutzutage wird dies immer seltener, aber wenn Sie irgendeine Komplikation haben, können Sie jede Spalte mit einem der nicht unterstützten Typen in einen unterstützten Typ zwingen. In der Praxis verwende ich im Allgemeinen eine Routine, die die Metadaten überprüft und dies automatisch ausführt, aber es ist nicht schön.
In einer funktionierenden Version möchten Sie wahrscheinlich die Liste der Spalten angeben, insbesondere wenn Sie eine explizite Erzwingung von Datentypen durchführen müssen oder wenn Sie nur bestimmte Spalten überprüfen,
Weder BINARY_CHECKSUM() noch seine einfache Schwester CHECKSUM() können Ihnen genau sagen, ob sich etwas in einer Zeile oder Tabelle geändert hat. Wir zeigen dies anhand der gebräuchlichen Wörter der englischen Sprache, die in einer Tabelle mit dem Namen CommonWords .. Sie würden erwarten, dass sie alle eine andere Prüfsumme haben, aber das ist nicht der Fall.
|
1
2
3
4
5 6
7
8
9
|
WÄHLEN SIE string, BINARY_CHECKSUM(string) ALS „Prüfsumme“
AUS commonWords
WHERE BINARY_CHECKSUM(string) IN
(
SELECT BINARY_CHECKSUM(string)
FROM commonwords
GRUPPIEREN NACH BINARY_CHECKSUM(string)
HAVING COUNT(*) > 2)
ORDER BY BINARY_CHECKSUM(string)
|
… das Ergebnis …
Bewaffnet mit diesen Informationen können wir schnell zeigen, dass verschiedene Strings die gleiche Prüfsumme haben können
|
1
2
3
|
WÄHLEN SIE BINARY_CHECKSUM(‚reed der Nerd‘),
BINARY_CHECKSUM(‚reed das Nest‘),
BINARY_CHECKSUM(’stud the oust‘)
|
All these will; have the same checksum, as would …
|
1
2
3
|
SELECT
BINARY_CHECKSUM(‚accosted guards‘),
BINARY_CHECKSUM(‚accorded feasts‘)
|
….während…
|
1
2
3
|
SELECT BINARY_CHECKSUM(‚Das sieht dem nächsten sehr ähnlich‘),
BINARY_CHECKSUM(‚das sieht dem nächsten sehr ähnlich‘),
BINARY_CHECKSUM(‚Das sieht dem Nächsten sehr ähnlich‘)
|
… gibt Ihnen verschiedene Prüfsummen wie diese …
|
1
2
|
———– ———– ———–
-447523377 -447522865 -447654449
|
The sister function CHECKSUM() …
|
1
2
3
4
|
SELECT CHECKSUM(‚This looks very much ‚),
CHECKSUM(‚das sieht sehr ähnlich aus wie das nächste‘),
CHECKSUM(‚Das sieht sehr ähnlich aus wie das Nächste‘)
|
… findet sie alle gleich, weil es die aktuelle Sortierung verwendet und meine Sortierung für die Datenbank die Groß- und Kleinschreibung nicht berücksichtigt. CHECKSUM() zielt darauf ab, Strings zu finden, die in der Prüfsumme gleich sind, wenn sie in einem String-Vergleich gleich sind.
|
1
2
|
———– ———– ———–
-943581052 -943581052 -943581052
|
Also, das Beste, was Sie sagen können, ist, dass es eine starke Wahrscheinlichkeit gibt, dass die Tabellen gleich sind, aber wenn Sie absolut sicher sein müssen, dann verwenden Sie einen anderen Algorithmus.
Wenn es Ihnen nichts ausmacht Unterschied im Fall in Textzeichenfolgen, dann können Sie CHECKSUM() anstelle von BINARY_CHECKSUM()
Der große Wert dieser Technik ist, dass, sobald Sie die Prüfsumme berechnet haben, die Sie benötigen, können Sie es als Wert in der Spalte einer Tabelle speichern, anstatt die ursprüngliche Tabelle zu benötigen und daher können Sie den gesamten Prozess noch schneller machen, und nehmen Sie weniger Zeit. Wenn Sie den von CHECKSUM() zurückgegebenen Prüfsummenwert speichern, stellen Sie sicher, dass Sie die Live-Tabelle mit einer Prüfsumme vergleichen, die mit derselben Sortierung generiert wurde.
Hier ist ein einfaches Beispiel für die ‚what’s changed‘-Routine.
|
1
2
3
4
5 6
7
8
9
10
11
12
13
14
15
|
– wir erstellen eine ‚checksum‘-Tabelle ‚on the fly‘ mit dem SELECT INTO .
SELECT
au_ID,
BINARY_CHECKSUM(au_id, au_lname, au_fname, phone, , city, , zip, ) AS
INTO auchk
FROM authorscopy
ORDER BY au_ID
/* Jetzt geben wir eine Einschränkung ein, um zu überprüfen, ob wir nicht im Lotto gewonnen haben (sehr unwahrscheinlich, aber nicht völlig unmöglich dass wir zwei Zeilen mit der gleichen Prüfsumme haben) */
ALTER TABLE AuChk ADD CONSTRAINT IsItUnique UNIQUE ()
UPDATE authorscopy SET au_fname=’Arthur‘
WHERE au_ID=’327-89-2366′
SELECT authorscopy.*
FROM authorscopy
INNER JOIN AuChk ON authorscopy.au_ID=AuChk.au_ID
WHERE <>BINARY_CHECKSUM(authorscopy.au_id, au_lname, au_fname, phone, , city, , zip, )
|
…which gives…
|
1
2
3
|
au_id au_lname au_fname phone address city state zip contract
———– ——— ——— ———— ————— ————- —– —– ——–
327-89-2366 Mendoza Arthur 529275-5757 15 Hague Blvd. Little Rock VON 98949 1
|
Und dann räumen wir einfach auf.
|
1
2
3
|
/* und wir kehren einfach zu dem zurück, was es war, als Teil des Teardowns */
UPDATE authorscopy SET au_fname=’Arnold‘
WHERE au_ID=’327-89-2366′
|
Natürlich können Sie einen Trigger verwenden, aber manchmal möchten Sie nur einen täglichen oder wöchentlichen Bericht über Änderungen, ohne dass ein Trigger in eine Tabelle eindringt.
XML verwenden
Eine allgemeine Möglichkeit besteht darin, die XML-Version der beiden Tabellen zu vergleichen, da dies die Datentypübersetzung in Zeichenfolgen für Sie durchführt. Es ist langsamer als der Prüfsummenansatz, aber zuverlässiger.
|
1
2
3
4
5
6
7
8
9
10
|
IF CONVERT(VARCHAR(MAX),(
SELECT *
FROM authors ORDER BY au_id FOR XML path, root))
=
CONVERT(VARCHAR(MAX),(
SELECT *
FROM authorscopy ORDER BY au_id FOR XMLpath, root))
SELECT ‚they are the same‘
ELSE
SELECT ‚they are different‘
|
Hier können Sie die Art des Vergleichs angeben, indem Sie die Sortierung angeben.
oder Sie können dies tun, indem Sie Daten in Tabellen vergleichen ..
|
1
2
3
4
5
6
7
8
9
10
|
IF BINARY_CHECKSUM(CONVERT(VARCHAR(MAX),(
SELECT *
FROM authors ORDER BY au_id FOR XML path, root)))
=
BINARY_CHECKSUM (CONVERT(VARCHAR(MAX),(
SELECT *
FROM authorscopy ORDER BY au_id FOR XML path, root)))
SELECT ‚they are pretty much the same‘
ELSE
SELECT ‚they are different‘ SELECT ‚they are different‘
|
… durch Berechnung einer Prüfsumme der XML-Version der Tabelle. Auf diese Weise können Sie die Prüfsumme der Tabelle speichern, mit der Sie vergleichen.
Finden, wo die Unterschiede in einer Tabelle sind
Die einfachste Aufgabe besteht darin, dass die Tabellen eine identische Anzahl von Zeilen und eine identische Tabellenstruktur haben. Manchmal möchten Sie wissen, welche Zeilen unterschiedlich sind und welche fehlen. Sie müssen natürlich angeben, was Sie mit ‚gleich‘ meinen, insbesondere wenn die beiden Tabellen unterschiedliche Spalten haben. Die Methode, die Sie für den Vergleich auswählen, wird im Allgemeinen durch diese Details bestimmt.
Die UNION ALL … GROUP BY-Technik
Der klassische Ansatz zum Vergleichen von Tabellen besteht darin, eine UNION ALL für die SELECT Anweisungen zu verwenden, die die Spalten enthalten, die Sie vergleichen möchten, und dann GROUP BY diese Spalten. Damit dies funktioniert, muss natürlich eine Spalte mit eindeutigen Werten in GROUP BY vorhanden sein, und der Primärschlüssel ist dafür ideal. Keine der beiden Tabellen darf dupliziert werden. Wenn sie eine unterschiedliche Anzahl von Zeilen haben, werden diese als Unterschiede angezeigt.
|
1
2
3
4
5 6
7
8
9
10
11
12
13
|
(
WÄHLEN SIE au_ID
AUS dem
(
WÄHLEN SIE au_id, au_lname, au_fname, Telefon, Adresse, Stadt, Bundesland, Postleitzahl, Vertrag
VON Autoren
UNION ALLE
WÄHLEN SIE au_id, au_lname, au_fname, Telefon, Adresse, Stadt, Bundesland, Postleitzahl, Vertrag
VON authorsCopy) BothOfEm
GRUPPE VON au_id, au_lname, au_fname, Telefon, Adresse, Stadt, Bundesland, Postleitzahl, Vertrag
HAVING COUNT(*)<2) f
|
Wenn eine der Tabellen ein Duplikat hat, dann wird es Ihnen ein falsches Ergebnis geben, wie hier, wo Sie zwei Tabellen haben, die sehr unterschiedlich sind und das Ergebnis sagt Ihnen, dass sie gleich sind! Aus diesem Grund ist es eine gute Idee, die Spalte (n) einzuschließen, die den Primärschlüssel bilden, und die Zeilen nur einmal einzuschließen!
|
1
2
3
4
5 6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
WÄHLEN SIE COUNT(*), Address_ID,TheAddress,ThePostCode
Address_ID,TheAddress,ThePostCode
VON
(
WERTE
(9, ‚929 Augustine lane, Staple Hill Ward South Gloucestershire UK‘,’BS16 4LL‘),
(10, ’45 Bradfield road, Parwich Derbyshire UK‘,’DE6 1QN‘)
) TableA(Address_ID, Die Adresse,die Postleitzahl)
UNION ALL
SELECT Address_ID,Die Adresse,die Postleitzahl
FROM
(
VALUES
(8, „‚The Pippins“, 20 Gloucester Pl, Chirton Ward, Tyne & Wear UK‘,’NE29 7AD‘),
(8, “’The Pippins“, 20 Gloucester Pl, Chirton Ward, Tyne & Wear UK‘,’NE29 7AD‘),
(9, ‚929 Augustine lane, Staple Hill Ward South Gloucestershire UK‘,’BS16 4LL‘),
(10, ’45 Bradfield road, Parwich Derbyshire UK‘,’DE6 1QN‘)
) TableB(Address_ID,TheAddress,ThePostCode)
)f
GROUP BY Address_ID,TheAddress,ThePostCode
HAVING COUNT(*)<2
|
… giving …
|
1
2
3
4
|
TheCount Address_ID TheAddress ThePostCode
———– ———– ————————- ————
(0 Zeile(n) betroffen)
|
Die Technik kann zum Vergleichen von mehr als zwei Tabellen verwendet werden. Sie müssen nur UNION ALL die Tabellen, die Sie vergleichen müssen, und ändern Sie die HAVING Klausel, um nur die Zeilen zu filtern, die nicht in allen Tabellen enthalten sind.
Mit EXCEPT
können Sie jetzt das viel sauberere und etwas schnellere EXCEPT .
|
1
2
3
|
SELECT * from authors
EXCEPT
SELECT * from authorsCopy
|
Hier werden alle Zeilen in authors angezeigt, die in authorsCopy nicht gefunden wurden. Wenn sie gleich sind, würde es keine Zeilen zurückgeben
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
au_id au_lname au_fname Telefon Adresse Stadt plz Vertrag
———– ———– ——— ———— ————————– ———– —– —– ——–
041-76-1076 Sosa Sonja 000-198-8753 29 Second Avenue Omaha CT 23243 0
187-42-2491 Mc Connell Trenton 0003090766 279 Den Haag Weg San Diego NY 94940 1
220-43-7067 Fox Judith 000-137-9418 269 East Hague Street Richmond VA 55027 0
505-28-2848 Hardy Mitchell 001-2479822 73 Grün Milton Antrieb Norfolk WA 69949 1
697-84-0401 Montes Leanne 000-018-0454 441 East Oak Parkway San Antonio MD 38169 1
727-35-9948 Lange Jonathon 000-8761152 280 Nobel Avenue Anchorage LA NULL 1
875-54-8676 Stein Keisha 000-107-1947 763 Weiß Fabien Way Fremont ND 08520 0
884-64-5876 Keller Steven 000-2787554 45 Weiß Nobel Boulevard Milwaukee NY 29108 1
886-75-9197 Ellis Marie 001032-5109 35 Osten Zweite Boulevard Chicago IL 32390 1
975-80-3567 Salazar Johanna 001-028-0716 17 New Boulevard Jackson ND 71625 0
(10 Zeile(n) betroffen)
|
Ich verwende SELECT * nur, um die Dinge für den Artikel einfach zu halten. Normalerweise listen Sie alle Spalten auf, die Sie vergleichen möchten.
Dies funktioniert nur für Tabellen mit der gleichen Anzahl von Zeilen, da, wenn Autoren zusätzliche Zeilen hätten, es immer noch sagen würde, dass sie anders waren, da die Zeilen in Autoren, die nicht in authorsCopy waren, zurückgegeben würden. Dies liegt daran, dass EXCEPT alle eindeutigen Werte aus der Abfrage links vom EXCEPT Operanden zurückgibt, die nicht auch aus der Abfrage rechts gefunden werden
Dies zeigt hoffentlich, was ich meine
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
WÄHLEN SIE Address_ID,TheAddress,ThePostCode
AUS
(WERTE
(9, ‚929 Augustine lane, Staple Hill Ward South Gloucestershire UK‘,’BS16 4LL‘),
(10, ’45 Bradfield road, Parwich Derbyshire UK‘,’DE6 1QN‘)
) TableA(Address_ID,TheAddress,
EXCEPT
SELECT Address_ID,TheAddress,ThePostCode from
(VALUES
(8, „‚Die Pippins“, 20 Gloucester Pl, Chirton Ward, Tyne & ‚ UK‘,’NE29 7AD‘),
(8, „‚Die Pippins“,‘ , 20 Gloucester Pl, Chirton Gemeinde, Tyne & Wear UK‘,’NE29 7AD‘),
(9, ‚929 Augustine lane, Staple Hill Ward South Gloucestershire UK‘,’BS16 4LL‘),
(10, ’45 Bradfield road, Parwich Derbyshire UK‘,’DE6 1QN‘)
) TableB(Address_ID,TheAddress,ThePostCode)
|
…yields …
|
1
2
3
4
|
Address_ID TheAddress Die Postleitzahl
———– ———————————————- ———–
(0 Zeile(n) betroffen)
|
…während …
|
1
2
3
4
5 6
7
8
9
10
11
12
13
14
|
Address_ID,TheAddress,ThePostCode VON
(WERTE
(8, „‚Die Pippins“, 20 Gloucester Pl, Chirton Ward, Tyne & ‚ UK‘,’NE29 7AD‘),
(8, „‚Die Pippins“, 20 Gloucester Pl, Chirton Ward, Tyne & “ UK‘,’NE29 7AD‘),
(9, ‚929 Augustine lane, Staple Hill Ward South Gloucestershire Vereinigtes Königreich‘,’BS16 4LL‘),
(10, ’45 Bradfield road, Parwich Derbyshire Vereinigtes Königreich‘,’DE6 1QN‘)
) TableB(Address_ID,TheAddress,ThePostCode )
EXCEPT
SELECT Address_ID,TheAddress,ThePostCode
VON
(WERTE
(9, ‚929 Augustine lane, Staple Hill Ward South Gloucestershire UK‘,’BS16 4LL‘),
(10, ’45 Bradfield road, Parwich Derbyshire UK‘,’DE6 1QN‘)
) TableA(Address_ID,TheAddress,ThePostCode)
|
..results in …
|
1
2
3
4
5
|
Address_ID TheAddress ThePostCode
———– ————————————————————- ———–
8 ‚The Pippins‘, 20 Gloucester Pl, Chirton Ward, Tyne & Wear UK NE29 7AD
(1 row(s) affected)
|
Diese Funktion von EXCEPT kann vorteilhaft verwendet werden, wenn Sie insbesondere überprüfen möchten, ob TableA in TableB enthalten ist. Wenn die Tabellen also eine unterschiedliche Anzahl von Zeilen haben, können Sie sie trotzdem vergleichen.
Möglicherweise möchten Sie nicht alle Spalten vergleichen. Sie sollten immer die Spalten angeben, die Sie vergleichen möchten, um die Gleichheit zu bestimmen. Wenn Sie beispielsweise nur die Adresse vergleichen möchten, verwenden Sie …
|
1
2
3
|
Adresse VON Autoren AUSWÄHLEN
AUßER
Adresse VON authorsCopy AUSWÄHLEN
|
Die Outer-Join-Technik
Es ist auch die Technik der äußeren Verbindung. Dies ist eine allgemeinere Technik, die Ihnen zusätzliche Einrichtungen bietet. Wenn Sie beispielsweise den vollständigen äußeren Join verwenden, können Sie die nicht übereinstimmenden Zeilen in beiden Tabellen abrufen. Dadurch erhalten Sie eine Vorher-Nachher-Ansicht der Änderungen in den Daten. Es wird allgemeiner in der Synchronisation verwendet, um Ihnen mitzuteilen, welche Zeilen gelöscht, eingefügt und aktualisiert werden sollen.
We’ll just use the technique to get the altered rows in authorsCopy
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SELECT authors.au_id, authors.au_lname, authors.au_fname, authors.phone, authors.address, authors.city, authors.state, authors.zip, authors.vertrag
VON Autoren
LEFT OUTER JOIN authorsCopy
AUF authors.au_ID = AuthorsCopy.au_ID
UND authors.au_lname =authorsCopy.au_lname
UND authors.au_fname =authorsCopy.au_fname
UND Autoren.telefon =authorsCopy.telefon
UND COALESCE(Autoren.adresse,“)=COALESCE(authorsCopy.adresse,“)
UND COALESCE(Autoren.stadt,“) =COALESCE(authorsCopy.stadt,“)
UND VERSCHMELZEN(Autoren.zustand,“) =COALESCE(authorsCopy.zustand,“)
UND VERSCHMELZEN(Autoren.zip,“) =COALESCE(authorsCopy.zip,“)
UND Autoren.vertrag =authorsCopy.contract
WHERE authorsCopy.au_ID IS NULL
|
Wie Sie sehen können, gibt es bei diesem Ansatz Schwierigkeiten mit Nullspalten, aber er ist so schnell wie die anderen und bietet Ihnen mehr Vielseitigkeit für Ihre Vergleiche.
Lokalisieren der Unterschiede zwischen Tabellen
Möglicherweise benötigen Sie eine schnelle Möglichkeit zu sehen, welche Spalte und Zeile sich geändert hat. Ein sehr genialer Weg, dies zu tun, wurde kürzlich veröffentlicht. Es verwendete XML. ‚Vergleichen Sie Tabellen und melden Sie die Unterschiede, indem Sie XML verwenden, um die Daten zu drehen‘ (Anmerkung der Redaktion: Link veraltet). Es ist klug, aber zu langsam. Das Gleiche kann rein in SQL gemacht werden. Grundsätzlich führen Sie einen spaltenweisen Vergleich von Daten basierend auf dem Primärschlüssel unter Verwendung eines Schlüssel / Wert-Paares durch. Wenn Sie die gesamte Tabelle auf einmal ausführen, ist dies ziemlich langsam: Der beste Trick besteht darin, dies nur in den Zeilen zu tun, in denen Sie wissen, dass es einen Unterschied gibt.
|
1
2
3
4
5 6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
|
temp TABLE(au_id VARCHAR(11) PRIMARY KEY) /*Dies enthält die Primärschlüssel von Zeilen, die sich geändert haben */
INSERT INTO @Temp(au_ID) –determine which rows have changed
SELECT au_ID
FROM –use the EXCEPT technique qwas in unseren Tests die schnellste ist
(
SELECT au_id, au_lname, au_fname, phone, , city, state, zip,
FROM ,
EXCEPT
SELECT au_id, telefon, adresse, Stadt, bundesland, Postleitzahl, Vertrag
VON authorsCopy
)f–jetzt wählen wir nur die Spalten aus, die sich geändert haben
SELECT lefthand.au_id,lefthand.name ,lefthand.wert WIE original,Righthand.wert ALS geändert
VON (–jetzt legen wir einfach die beiden Tabellen als Schlüsselwertpaare an und verwenden die Zeichenfolgenversionen der Daten
SELECT authors.au_id, ‚au_lname‘ AS ’name‘,au_lname AS ‚value‘
FROM authors INNER JOIN @Temp altered ON altered.au_id=authors.au_id
UNION
SELECT authors.au_id, ‚au_fname‘ AS ’name‘, au_fname AS ‚value‘
FROM authors INNER JOIN @Temp altered ON altered.au_id=authors.au_id
UNION
WÄHLEN SIE authors.au_id, ‚phone‘,phone
FROM authors INNER JOIN @Temp altered ON altered AUS.au_id=authors.au_id
UNION
WÄHLEN SIE authors.au_id, ‚adresse‘,Adresse
VON authors INNER JOIN @Temp altered AUF altered.au_id=authors.au_id
UNION
WÄHLEN SIE authors.au_id, ‚Stadt‘ ALS ’name‘, Stadt ALS ‚Wert‘
VON authors INNER JOIN @Temp altered AUF altered.au_id= >
UNION
SELECT authors.au_id, ‚State‘,state
FROM authors INNER JOIN @Temp altered ON altered.au_id=authors.au_id
UNION
Autoren AUSWÄHLEN.au_id, ‚zip‘,zip
VON Autoren INNER JOIN @Temp geändert AUF altered.au_id=authors.au_id
UNION
SELECT authors.au_id, ‚contract‘,CONVERT(CHAR(1),contract)
VON Autoren INNER JOIN @Temp geändert AUF altered.au_id=authors.au_id) LeftHand
INNER JOIN (
SELECT authorsCopy.au_id , ‚au_lname‘ ALS ’name‘,au_lname ALS ‚Wert‘
VON authorsCopy INNER JOIN @Temp geändert AUF altered.au_id=authorsCopy.au_id
UNION
SELECT authorsCopy.au_id, ‚au_fname‘,au_fname
VON authorsCopy INNER JOIN @Temp geändert AUF altered.au_id=authorsCopy.au_id
UNION
SELECT authorsCopy.au_id, ‚phone‘,phone
VON authorsCopy INNER JOIN @Temp geändert AUF altered.au_id=authorsCopy.au_id
UNION
SELECT authorsCopy. au_id, ‚adresse‘,Adresse
VON authorsCopy INNER JOIN @Temp geändert AUF altered.au_id=authorsCopy.au_id
UNION
SELECT authorsCopy.au_id, ‚Stadt‘ ALS ’name‘,Stadt ALS ‚Wert‘
VON authorsCopy INNER JOIN @Temp altered AUF altered.au_id=authorsCopy.au_id
UNION
WÄHLEN SIE authorsCopy.au_id, ‚State‘,state
VON authorsCopy INNER JOIN @Temp altered AUF altered.au_id=authorsCopy.au_id
UNION
WÄHLEN SIE authorsCopy. au_id, ‚zip‘,zip
VON authorsCopy INNER JOIN @Temp geändert AUF altered.au_id=authorsCopy.au_id
UNION
WÄHLEN SIE authorsCopy.au_id, ‚contract‘,CONVERT(CHAR(1),contract)
VON authorsCopy INNER JOIN @Temp geändert AUF altered.au_id=authorsCopy.au_id) rightHand
AUF lefthand.au_ID=righthand.au_ID
UND lefthand.name=righthand.name
WOBEI lefthand.wert<>rechte Hand.wert
|
In unserem Beispiel würde dies ergeben:
|
1
2
3
4
5 6
7
8
9
10
11
12
|
———– ——– —————————- ————————————
041-76-1076 Adresse 29 Second Avenue 9 Second Avenue
187-42-2491 Adresse 279 Haager Weg 79 Haager Weg
220-43-7067 Adresse 269 East Hague Street 69 East Hague Street
505-28-2848 Adresse 73 Green Milton Drive 3 Green Milton Drive
697-84-0401 Adresse 441 East Oak Parkway 41 East Oak Parkway
727-35-9948 Adresse 280 Nobel Avenue 80 Nobel Avenue
875-54-8676 Adresse 763 White Fabien Way 63 White Fabien Way
884-64-5876 Adresse 45 White Nobel Boulevard 5 White Nobel Boulevard
886-75-9197 Adresse 35 East Second Boulevard 5 East Second Boulevard
975-80-3567 Adresse 17 Neuer Boulevard 7 Neuer Boulevard
|
Diese Technik dreht die Zeilen der Tabellen, die Unterschiede aufweisen, in eine EAV-Tabelle (Entity-Attribute-Value), sodass Unterschiede innerhalb einer Zeile verglichen und angezeigt werden können. Es führt diese Rotation durch, indem UNIONden Namen und den String-Wert jeder Spalte eingibt. Diese Technik funktioniert am besten, wenn es nicht viele Unterschiede gibt.
Schlussfolgerungen
Es gibt keine einzige ideale Methode, um die Daten in Tabellen oder Ergebnissen zu vergleichen. Eine von mehreren Techniken wird für eine bestimmte Aufgabe am relevantesten sein. Es hängt alles von genau den Antworten ab, die Sie benötigen, und von der Art der Aufgabe. Benötigen Sie eine schnelle Überprüfung, ob sich eine Tabelle nicht geändert hat, oder müssen Sie genau wissen, um welche Änderungen es sich handelt? SQL ist natürlich schnell bei dieser Aufgabe und Vergleiche von Tabellen und Ergebnissen ist eine vertraute Aufgabe für viele Datenbankentwickler.
Wenn es eine allgemeine Regel gibt, würde ich sagen, dass explorative oder Ad-hoc-Arbeit ein Tool wie SQL Data Compare benötigt, während ein Routineprozess innerhalb der Datenbank eine handgeschnittene SQL-Technik erfordert.