Redgate Hub
Inleiding
zijn er verschillende redenen waarom u mogelijk tabellen of resultaten moet vergelijken.
- soms moet men alleen weten of de tabellen dezelfde of andere gegevens bevatten; geen details: gewoon ja of nee. Dit is typisch voor test beweringen, waar je hoeft alleen maar te weten of uw routine of batch produceert een resultaat met de juiste gegevens erin. indien voorzien van specifieke vales voor de parameters. Het is fout of juist
- af en toe moet u weten welke rijen zijn veranderd zonder, misschien, specifiek te zijn over welke kolommen zijn veranderd en hoe.
- Er zijn momenten waarop u een grote tabel hebt in termen van zowel kolommen als rijen, en u hebt iets nodig dat u specifiek de kolom(s) toont die hun waarde hebben gewijzigd. Je zou dit ook willen bij het opsporen van een bug in een routine die anders zou kunnen vereisen dat u om tijd te verspillen scannen ‘met het oog’.
We zullen deze drie nogal verschillende taken aanpakken in SQL
als twee tabellen een verschillend aantal rijen hebben, kunnen ze natuurlijk niet hetzelfde zijn. Er zijn echter momenten waarop je moet weten of Table_B alle rijen van Table_A bevat, zonder verschillen. Als u meer details wenst, wilt u misschien zelfs de rijen in een van de tabellen kennen die niet gemeenschappelijk zijn, of de gemeenschappelijke rijen, zoals aangegeven door de primaire sleutel, die anders waren. Waarom alleen twee tafels vergelijken? Er zijn manieren om zoveel te vergelijken als je nodig hebt. (zoals, bijvoorbeeld, wanneer u de metadata in verschillende database snapshots vergelijkt). Ja, er zijn veel variaties
u hebt toch tools en functies om dit te doen?
Er is altijd een plaats voor tools zoals SQL Data Compare, TableDiff, tSQLt of Change Data Capture. Veel hangt af van de omstandigheden en het soort taak. Het probleem van het doen van audits op Wijzigingen in gegevens in een live systeem is een apart onderwerp, net als de synchronisatie van tabellen en databases. Vergelijking van XML-documenten valt ook buiten het bereik. We gaan puur om met de routinematige vergelijking van de gegevens in tabellen
Ik gebruik waarschijnlijk TSQL technieken om tabellen te vergelijken wanneer:
ontwikkelen…
In de loop van het ontwikkelen van een database worden veel tabellen vergeleken. Het is niet alleen het grote spul: elke tafel-gewaardeerde functie, bijvoorbeeld, heeft een test harnas nodig in het build script dat ervoor zorgt dat het doet wat je denkt dat het moet doen onder alle denkbare testomstandigheden, en het opnemen van alle vervelende rand gevallen waar het is gevangen door de testers in het verleden. Elke opgeslagen procedure heeft een test nodig om ervoor te zorgen dat het proces dat het uitvoert precies doet wat bedoeld is en niets anders.
Er was een tijd dat de build-activiteit nogal ontspannen was, maar als je een nachtelijke build – en integratietest hebt, is het het beste om deze volledig te automatiseren en van het karwei af te zijn.
ETL
wanneer u het laden van gegevens in een systeem automatiseert, moet u vaak verschillende omstandigheden testen. Moet u bestaande versies van de rijen bijwerken en de nieuwe invoegen? Heeft u een val nodig om dubbele vermeldingen te voorkomen, of zelfs bestaande vermeldingen te verwijderen?
de testgegevens instellen.
De scripts in dit artikel gebruiken allemaal een tabel uit de eerbiedwaardige PUBS database. We gaan de auteurs tabel gebruiken, maar zal het aantal rijen een beetje verhogen tot 5000 om een grootte te krijgen die een beetje realistischer is. Ik heb de bron voor de tafel voorzien met het artikel.
I then created a copy of the table …
|
1
2
3
4
5
6
|
SELECT * INTO authorsCopy
FROM authors
GO
ALTER TABLE dbo.authorsCopy voegt beperking PK_authorsCopy primaire sleutel geclusterd
(au_id) op primaire
GO
|
en veranderde vervolgens enkele rijen.
|
1
2
3
4
5
|
UPDATE authorsCopy adres=STUFF(adres,1,1,”)
au_ID IN (
SELECTEER TOP 10 au_id
VAN authorsCopy f
OM DOOR de telefoon)
|
Dus nu de twee tabellen moeten worden overwegend het zelfde met een paar kleine wijzigingen in het adres veld
Testen om te zien of tabellen anders zijn.
soms wilt u gewoon weten of tabellen hetzelfde zijn. Een voorbeeld hiervan is het controleren of een TVF goed werkt door het resultaat te vergelijken met dat van een bestaande tabel met de juiste resultaten. De gebruikelijke manier om dit te doen is met de CHECKSUM()groep van functies in SQL Server, omdat ze erg snel zijn.
met behulp van Checksums
kunt u de functie BINARY_CHECKSUM gebruiken om te controleren of tabellen hetzelfde zijn: goed, ongeveer hetzelfde. Het is snel, maar niet perfect, zoals ik zo zal aantonen. Als je een reeks tests hebt, bijvoorbeeld, is het over het algemeen voldoende.
|
1
2
3
4
5
6
7
8
9
|
IF (
SELECTEER CHECKSUM_AGG(BINARY_CHECKSUM(*))
VAN de auteurs)=(
SELECTEER CHECKSUM_AGG(BINARY_CHECKSUM(*))
VAN authorsCopy)
KIES ‘ze zijn waarschijnlijk dezelfde’
ANDERS
KIES ‘ze zijn anders-zijn’
|
om dit te laten werken, mag uw tabel niet TEXT, NTEXT, IMAGE or CURSOR (of een SQL_VARIANT met een van deze types) als basistype hebben. Tegenwoordig is dit steeds zeldzamer, maar als u een soort complicatie hebt, kunt u elke kolom dwingen met een van de niet-ondersteunde typen in een ondersteund type. In de praktijk gebruik ik meestal een routine die de metadata controleert en dit automatisch doet, maar het is niet mooi.
in een werkende versie zou u waarschijnlijk de lijst met kolommen willen specificeren, vooral als u een expliciete dwang van datatypes moet uitvoeren, of als u slechts bepaalde kolommen controleert,
noch BINARY_CHECKSUM() noch zijn gewone zus CHECKSUM() zijn volledig accuraat in het vertellen of er iets is veranderd in een rij of tabel. We zullen dit laten zien door te kijken naar de algemene woorden van de Engelse taal, opgenomen in een tabel genaamd CommonWords.. Je zou verwachten dat ze allemaal een andere checksum hebben, maar dat is niet het geval.
|
1
2
3
4
5
6
7
8
9
|
SELECTEER string, BINARY_CHECKSUM(string) ALS “Checksum”
VAN commonWords
WAAR BINARY_CHECKSUM(string) IN
(
SELECTEER BINARY_CHECKSUM(string)
VAN commonwords
GROEP DOOR BINARY_CHECKSUM(string)
HAVING COUNT(*) > 2)
OM DOOR BINARY_CHECKSUM(string)
|
… het geven van het resultaat …
Gewapend met deze informatie, we kunnen snel aantonen dat verschillende snaren kunnen dezelfde checksum
|
1
2
3
|
SELECTEER BINARY_CHECKSUM(‘reed de nerd’),
BINARY_CHECKSUM(‘opgeroepen het 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’)
|
….terwijl de…
|
1
2
3
|
SELECTEER BINARY_CHECKSUM(‘Dit lijkt heel erg op de volgende’),
BINARY_CHECKSUM(‘dit lijkt heel erg op de volgende’),
BINARY_CHECKSUM(‘Dit lijkt heel erg op de Volgende’)
|
… geeft u verschillende controlesommen als dit…
|
1
2
|
———– ———– ———–
-447523377 -447522865 -447654449
|
The sister function CHECKSUM() …
|
1
2
3
4
|
SELECT CHECKSUM(‘This looks very much net als de volgende’),
CHECKSUM(‘dit lijkt erg op de volgende’),
CHECKSUM(‘dit lijkt erg op de volgende’)
|
… vindt dat ze allemaal hetzelfde zijn, omdat het de huidige collatie en mijn collatie gebruikt voor de database is niet hoofdlettergevoelig. CHECKSUM() heeft tot doel strings gelijk te vinden in checksum als ze gelijk zijn in een stringvergelijking.
|
1
2
|
———– ———– ———–
-943581052 -943581052 -943581052
|
Dus, het beste wat je kan zeggen is dat er een grote kans is dat de tabellen zal hetzelfde zijn, maar als je nodig hebt om helemaal zeker te zijn, gebruik dan een ander algoritme.
Als u het verschil in hoofdletters in tekstreeksen niet erg vindt, dan kunt u CHECKSUM() gebruiken in plaats van BINARY_CHECKSUM()
De grote waarde van deze techniek is dat, zodra u de controlesom hebt berekend die u nodig hebt, u deze kunt opslaan als een waarde in de kolom van een tabel in plaats van de oorspronkelijke tabel nodig te hebben en u daardoor het hele proces nog sneller kunt maken, en minder tijd kunt nemen. Als u de checksum waarde opslaat die wordt geretourneerd door CHECKSUM() controleer dan met de live tabel met een checksum gegenereerd met dezelfde collatie.
Hier is een eenvoudig voorbeeld van de’ Wat is veranderd ‘ routine.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
–we maken een ‘checksum’ tabel ‘on the fly’ met de selectie IN.
SELECTEER
au_ID,
BINARY_CHECKSUM(au_id, au_lname, au_fname, telefoon , stad , zip ) ALS
IN auchk
VAN authorscopy
OM DOOR au_ID
/* we gaan nu in een beperking gewoon om te controleren dat we nog niet gewonnen in de loterij (zeer onwaarschijnlijk, maar niet geheel onmogelijk dat we twee rijen met dezelfde checksum) */
ALTER TABLE AuChk ADD CONSTRAINT IsItUnique UNIEKE ()
UPDATE authorscopy SET au_fname=’Arthur’
au_ID=’327-89-2366′
SELECTEER 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 van 98949 1
|
en dan opruimen we gewoon.
|
1
2
3
|
/* en we gewoon pop terug naar wat het was, als onderdeel van de teardown */
UPDATE authorscopy SET au_fname=’Arnold’
au_ID=’327-89-2366′
|
natuurlijk, u kan gebruik maken van een trigger, maar soms wil je gewoon een dagelijks of wekelijks overzicht van wijzigingen zonder de inmenging van een trigger in een tabel.
met behulp van XML
een algemene mogelijkheid is om de XML-versie van de twee tabellen te vergelijken, omdat dit de datatype-vertaling in tekenreeksen voor u doet. Het is langzamer dan de Checksum aanpak, maar betrouwbaarder.
|
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 kunt u het type vergelijking specificeren door de collatie op te geven.
of u kunt dit doen door gegevens in tabellen te vergelijken ..
|
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 quasi same’
ELSE
SELECT’ they are different ‘SELECT’ they are different ‘
|
… door een controlesom van de XML-versie van de tabel te berekenen. Hiermee kunt u de checksum opslaan van de tabel waarmee u vergelijkt.
zoeken waar de verschillen zich in een tabel bevinden
De eenvoudigste taak is wanneer de tabellen een identiek aantal rijen en een identieke tabelstructuur hebben. Soms wil je weten welke rijen verschillend zijn, en welke ontbreken. U moet natuurlijk aangeven wat u bedoelt met “hetzelfde”, vooral als de twee tabellen verschillende kolommen hebben. De methode die u kiest om de vergelijking te doen wordt over het algemeen bepaald door deze gegevens.
the UNION ALL … GROUP BY technique
De klassieke benadering om tabellen te vergelijken is om een UNION ALL te gebruiken voor de SELECT statements die de kolommen bevatten die u wilt vergelijken, en vervolgens GROUP BY die kolommen. Om dit te laten werken moet er natuurlijk een kolom zijn met unieke waarden in de GROUP BY, en de primaire sleutel is hiervoor ideaal. Geen van beide tabellen zijn toegestaan duplicaten. Als ze verschillende aantallen rijen hebben, worden deze weergegeven als verschillen.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SELECT DISTINCT au_ID
VAN de
(
SELECTEER au_ID
VAN de
(
SELECTEER au_id, au_lname, au_fname, telefoon, adres, stad, staat en zip, het contract
VAN auteurs
UNIE
SELECTEER au_id, au_lname, au_fname, telefoon, adres, stad, staat en zip, het contract
VAN authorsCopy) BothOfEm
GROEP DOOR au_id, au_lname, au_fname, telefoon, adres, stad, staat en zip, het contract
HAVING COUNT(*)<2) f
|
Als een van de tabellen is een dubbele, dan zal het geeft je een vals resultaat, zoals hier, waar je op twee tabellen die zijn zeer verschillend en het resultaat vertelt u dat ze hetzelfde zijn! Om deze reden is het een goed idee om de kolom(s) die de primaire sleutel vormen op te nemen, en alleen de rijen één keer op te nemen!
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
SELECT COUNT(*), Address_ID,TheAddress,ThePostCode
VAN de
(
SELECTEER Address_ID,TheAddress,ThePostCode
VAN
(
WAARDEN
(9, ‘929 Augustinus lane, Nieten Hill Wijk South Gloucestershire UK’,’BS16 4LL’),
(10, ’45 Bradfield weg, Parwich Derbyshire, UK’,’DE6 1QN’)
) tabel a(Address_ID,TheAddress,ThePostCode)
UNION
SELECTEER Address_ID,TheAddress,ThePostCode
VAN
(
WAARDEN
(8, ‘De Pippins”, 20 Gloucester Pl, Chirton Wijk, 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 rij(en) beïnvloed)
|
De techniek kan worden gebruikt voor het vergelijken van meer dan twee tabellen. U hoeft alleen maar UNION ALL de tabellen te vergelijken en de HAVING clausule te wijzigen om alleen de rijen te filteren die niet in alle tabellen staan.
met uitzondering van
kunt u nu de veel schonere en iets snellere EXCEPTgebruiken.
|
1
2
3
|
SELECT * from auteurs
EXCEPT
SELECT * from authorsCopy
|
Dit laat alle rijen in auteurs die niet zijn gevonden in authorsCopy. Als ze hetzelfde zijn, het zou niet terugkeren rijen
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
au_id au_lname au_fname telefoon adres plaats land postcode contract
———– ———– ——— ———— ————————– ———– —– —– ——–
041-76-1076 Sosa Sonja 000-198-8753 29 Second Avenue Omaha CT 23243 0
187-42-2491 Mc Connell Trenton 0003090766 279 Haag Manier San Diego NY 94940 1
220-43-7067 Vos Judith 000-137-9418 269 Oosten Haag Street Richmond VA 55027 0
505-28-2848 Hardy Mitchell 001-2479822 73 Groene Milton Rijden Norfolk WA 69949 1
697-84-0401 Montes Leanne 000-018-0454 441 Oosten Oak Parkway San Antonio MD 38169 1
727-35-9948 Lange Jonathon 000-8761152 280 Nobel Avenue Anchorage LA NULL 1
875-54-8676 Steen Keisha 000-107-1947 763 Wit Fabien Manier Fremont ND 08520 0
884-64-5876 Keller Steven 000-2787554 45 Wit Nobel Boulevard Milwaukee NY 29108 1
886-75-9197 Ellis Marie 001032-5109 35 East Tweede Boulevard Chicago IL 32390 1
975-80-3567 Salazar Johanna 001-028-0716 17 Nieuwe Boulevard Jackson ND 71625 0
(10 rij(en) beïnvloed)
|
ik ben alleen met SELECT * om het eenvoudig te houden voor het artikel. Normaal zou je alle kolommen die je wilt vergelijken specificeren.
dit werkt alleen voor tabellen met hetzelfde aantal rijen omdat, als auteurs extra rijen hadden, het nog steeds zou zeggen dat ze anders waren, omdat de rijen in auteurs die niet in authorsCopy waren zouden worden teruggegeven. Dit is omdat EXCEPT geeft als resultaat een unieke waarden van de query aan de linkerkant van de EXCEPT operand die niet ook gevonden van de query op de juiste
Dit hopelijk ziet u wat ik bedoel
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
SELECTEER Address_ID,TheAddress,ThePostCode
VAN
WAARDE
(9, ‘929 Augustinus lane, Nieten Hill Wijk South Gloucestershire UK’,’BS16 4LL’),
(10, ’45 Bradfield weg, Parwich Derbyshire, UK’,’DE6 1QN’)
) tabel a(Address_ID,TheAddress,ThePostCode)
EXCEPT
SELECTEER Address_ID,TheAddress,ThePostCode van
WAARDE
(8, ‘De Pippins”, 20 Gloucester Pl, Chirton Wijk, Tyne & Draag UK’,’NE29 7AD’),
(8, ‘De Pippins”, 20 Gloucester Pl, Chirton Wijk, 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 ThePostCode
———– ———————————————- ———–
(0 rij(en) beïnvloed)
|
…overwegende …
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
SELECTEER Address_ID,TheAddress,ThePostCode VAN
WAARDE
(8, ‘De Pippins”, 20 Gloucester Pl, Chirton Wijk, Tyne & Draag UK’,’NE29 7AD’),
(8, ‘De Pippins”, 20 Gloucester Pl, Chirton Wijk, Tyne & Draag UK’,’NE29 7AD’),
(9, ‘929 Augustinus lane, Nieten Hill Wijk South Gloucestershire UK’,’BS16 4LL’),
(10, ’45 Bradfield weg, Parwich Derbyshire, UK’,’DE6 1QN’)
) Tabel b(Address_ID,TheAddress,ThePostCode)
EXCEPT
SELECTEER Address_ID,TheAddress,ThePostCode
van
(waarden
(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)
|
Deze functie van EXCEPT kan gebruikt worden als u in het bijzonder wilt controleren of TableA is opgenomen in TableB. Dus waar de tabellen een ander aantal rijen hebben kun je ze nog steeds vergelijken.
mogelijk wilt u niet alle kolommen vergelijken. U moet altijd de kolommen specificeren die u wilt vergelijken om ‘gelijkheid’te bepalen. Als u wilde alleen maar om te vergelijken met het Adres van bijvoorbeeld u zou gebruiken …
|
1
2
3
|
SELECTEER adres VAN auteurs
EXCEPT
SELECTEER adres VAN authorsCopy
|
De Outer Join-techniek
Er is ook de techniek van de outer join. Dit is een meer algemene techniek die u extra faciliteiten geven. Als u bijvoorbeeld de volledige buitenste join gebruikt, kunt u de ongeëvenaarde rijen in beide tabellen krijgen. Dit geeft u een’ voor ‘en’ na ‘ weergave van wijzigingen in de gegevens. Het wordt meer algemeen gebruikt in synchronisatie om u te vertellen welke rijen te verwijderen, in te voegen en bij te werken.
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.contract
VAN auteurs
LEFT OUTER JOIN authorsCopy
OP auteurs.au_ID = AuthorsCopy.au_ID
EN auteurs.au_lname =authorsCopy.au_lname
EN auteurs.au_fname =authorsCopy.au_fname
EN auteurs.telefoon = auteurscopy.phone
en COALESCE (auteurs.adres,”)=COALESCE(authorsCopy.adres,”)
en COALESCE(auteurs.city,”) = COALESCE(authorsCopy.city,”)
en COALESCE(auteurs.staat,”) = COALESCE (authorsCopy.staat,”)
en COALESCE(auteurs.zip,”) = COALESCE (authorsCopy.zip,”)
en auteurs.contract = auteurscopy.contract
waar authorsCopy. au_ID NULL
|
zoals u kunt zien, zijn er problemen met null kolommen met deze aanpak, maar het is net zo snel als de andere en het geeft u iets meer veelzijdigheid voor uw vergelijkingen.
het lokaliseren van de verschillen tussen tabellen
u hebt mogelijk een snelle manier nodig om te zien welke kolom en rij zijn veranderd. Een zeer ingenieuze manier om dit te doen werd onlangs gepubliceerd. Het gebruikte XML. ‘Vergelijk tabellen en rapporteer de verschillen met behulp van Xml om de gegevens te draaien’ (noot van de editor: link verouderd). Het is slim, maar te traag. Hetzelfde kan puur in SQL. In principe voert u een kolom voor kolom vergelijking van gegevens uit op basis van de primaire sleutel, met behulp van een sleutel/waarde paar. Als je de hele tabel tegelijk doet is het nogal traag: de beste truc is om dit alleen te doen op die Rijen waar je weet dat er een verschil is.
|
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
|
met VERMELDING van @temp TABEL(au_id VARCHAR(11) PRIMAIRE SLEUTEL) /*dit is de primaire sleutels van de rijen die zijn veranderd */
INVOEGEN IN @Temp(au_ID) –bepalen welke rijen zijn gewijzigd
SELECTEER au_ID
–gebruik VAN de UITZONDERING van techniek qhich is de snelste in onze tests
(
SELECTEER au_id, au_lname, au_fname, telefoon , stad, staat en zip,
VAN auteurs
EXCEPT
SELECTEER au_id, au_lname, au_fname, telefoon, adres, stad, staat en zip, het contract
VAN authorsCopy
)f–nu we SELECTEER de kolommen die zijn veranderd
SELECTEER links.au_id,linksaf.naam,linksaf.waarde ALS het origineel,Rechts.de waarde ZOALS gewijzigd
VAN (–nu we net de lay-out van de twee tabellen als sleutel / waarde-paren, met behulp van de string versies van de data
SELECTEER auteurs.au_id, ‘au_lname’ ALS ‘naam’,au_lname ALS ‘waarde’
VAN auteurs INNER JOIN @Temp gewijzigd OP veranderd.au_id=auteurs.au_id
UNION
SELECTEER auteurs.au_id, ‘au_fname’ ALS ‘naam’,au_fname ALS ‘waarde’
VAN auteurs INNER JOIN @Temp gewijzigd OP veranderd.au_id=auteurs.au_id
UNION
SELECTEER auteurs.au_id, ’telefoon’,telefoon
VAN auteurs INNER JOIN @Temp gewijzigd OP veranderd.au_id=auteurs.au_id
UNION
SELECTEER auteurs.au_id, ‘adres’,adres
VAN auteurs INNER JOIN @Temp gewijzigd OP veranderd.au_id=auteurs.au_id
UNION
SELECTEER auteurs.au_id ‘Stad’ ALS ‘naam’,de Stad ALS ‘waarde’
VAN auteurs INNER JOIN @Temp gewijzigd OP veranderd.au_id=auteurs.au_id
UNION
SELECTEER auteurs.au_id, ‘Staat’,staat
VAN auteurs INNER JOIN @Temp gewijzigd OP veranderd.au_id=auteurs.au_id
UNION
SELECTEER auteurs.au_id, ‘zip’,zip
VAN auteurs INNER JOIN @Temp gewijzigd OP veranderd.au_id=auteurs.au_id
UNION
SELECTEER auteurs.au_id, ‘overeenkomst’,CONVERT(CHAR(1),contract)
VAN auteurs INNER JOIN @Temp gewijzigd OP veranderd.au_id=auteurs.au_id) Links
INNER JOIN (
SELECTEER authorsCopy.au_id, ‘au_lname’ ALS ‘naam’,au_lname ALS ‘waarde’
VAN authorsCopy INNER JOIN @Temp gewijzigd OP veranderd.au_id=authorsCopy.au_id
UNION
SELECTEER authorsCopy.au_id, ‘au_fname’,au_fname
VAN authorsCopy INNER JOIN @Temp gewijzigd OP veranderd.au_id=authorsCopy.au_id
UNION
SELECTEER authorsCopy.au_id, ’telefoon’,telefoon
VAN authorsCopy INNER JOIN @Temp gewijzigd OP veranderd.au_id=authorsCopy.au_id
UNION
SELECTEER authorsCopy.au_id, ‘adres’,adres
VAN authorsCopy INNER JOIN @Temp gewijzigd OP veranderd.au_id=authorsCopy.au_id
UNION
SELECTEER authorsCopy.au_id ‘Stad’ ALS ‘naam’,de Stad ALS ‘waarde’
VAN authorsCopy INNER JOIN @Temp gewijzigd OP veranderd.au_id=authorsCopy.au_id
UNION
SELECTEER authorsCopy.au_id, ‘Staat’,staat
VAN authorsCopy INNER JOIN @Temp gewijzigd OP veranderd.au_id=authorsCopy.au_id
UNION
SELECTEER authorsCopy.au_id, ‘zip’,zip
VAN authorsCopy INNER JOIN @Temp gewijzigd OP veranderd.au_id=authorsCopy.au_id
UNION
SELECTEER authorsCopy.au_id, ‘contract’, CONVERT(CHAR (1),contract)
FROM authorsCopy INNER JOIN @Temp altered ON altered.au_id=authorsCopy.au_id) rightHand
op links.au_ID=righthand.au_ID
en lefthand.name=righthand.name
waar links.waarde<> rechts.waarde
|
in ons voorbeeld zou dit geven:
|
1
2
3
4
5
6
7
8
9
10
11
12
|
au_id oorspronkelijke naam veranderd
———– ——– —————————- ————————————
041-76-1076 adres 29 Second Avenue 9 Second Avenue
187-42-2491 adres 279 Haag Manier 79 Haag Manier
220-43-7067 adres 269 Oosten Haag Straat 69-Oosten Haag Street
505-28-2848 adres 73 Groene Milton Schijf 3 Groene Milton Schijf
697-84-0401 adres 441 Oosten Oak Parkway 41 Oost-Oak Parkway
727-35-9948 adres 280 Nobel Avenue 80 Nobel Avenue
875-54-8676 adres 763 Wit Fabien Manier 63 Witte Fabien Manier
884-64-5876 adres 45 Wit Nobel Boulevard 5 Wit Nobel Boulevard
886-75-9197 adres 35 East Tweede Boulevard 5-Oosten Tweede Boulevard
975-80-3567 adres 17 Nieuwe Boulevard 7 Nieuwe Boulevard
|
Deze techniek roteert de rijen van de tabellen die verschillen hebben in een Entity-attribute-value (EAV) tabel, zodat verschillen binnen een Rij kunnen worden vergeleken en weergegeven. Het doet deze rotatie met UNIONen de naam en de string-waarde van elke kolom. Deze techniek werkt het beste waar er niet een groot aantal verschillen.
conclusies
Er bestaat geen ideale methode om de gegevens in tabellen of resultaten te vergelijken. Een van een aantal technieken zal het meest relevant zijn voor een bepaalde taak. Het komt allemaal neer op precies de antwoorden die u nodig hebt en het type taak. Moet u snel controleren of een tabel niet is veranderd, of moet u precies weten wat de wijzigingen zijn? SQL is van nature snel in het doen van deze taak en vergelijkingen van tabellen en resultaten is een bekende taak voor veel database-ontwikkelaars.
als er een algemene regel is, zou ik zeggen dat verkennend of ad-hoc werk een tool nodig heeft zoals SQL Data Compare, terwijl een routine proces binnen de database een handgesneden SQL techniek vereist.