Redgate Hub
Introduction
Il existe plusieurs raisons pour lesquelles vous devrez peut-être comparer des tables ou des résultats.
- Parfois, il suffit de savoir si les tables contiennent des données identiques ou différentes; Pas de détails: juste oui ou non. Ceci est typique des assertions de test, où vous avez juste besoin de savoir si votre routine ou votre lot produit un résultat avec les bonnes données. lorsqu’il est fourni avec des valeurs particulières pour les paramètres. C’est faux ou juste
- De temps en temps, vous devez savoir quelles lignes ont changé sans, peut-être, être particulier sur les colonnes qui ont changé et comment.
- Il y a des moments où vous avez une grande table en termes de colonnes et de lignes, et vous avez besoin de quelque chose qui vous montre spécifiquement la ou les colonnes qui ont changé leur valeur. Vous voudrez peut-être également cela lorsque vous traquez un bogue dans une routine qui pourrait autrement vous obliger à perdre du temps à scanner « à l’œil ».
Nous allons nous attaquer à ces trois tâches assez différentes en SQL
Si deux tables ont un nombre de lignes différent, elles ne peuvent bien sûr pas être les mêmes. Cependant, il y a des moments où vous devez savoir si Table_B contient toutes les lignes de Table_A, sans différences. Si vous souhaitez plus de détails, vous souhaiterez peut-être même connaître les lignes de la table qui ne sont pas communes ou les lignes communes, comme indiqué par la clé primaire, qui étaient différentes. Pourquoi s’en tenir à comparer seulement deux tableaux? Il existe des moyens de comparer autant que vous en avez besoin. (comme, par exemple, lorsque vous comparez les métadonnées dans plusieurs instantanés de base de données). Oui, il existe de nombreuses variantes
Vous avez des outils et des fonctionnalités pour faire ce genre de choses, sûrement?
Il y a toujours une place pour des outils comme SQL Data Compare, TableDiff, tSQLt ou Change Data Capture. Tout dépend des circonstances et du type de tâche. Le problème des audits sur les modifications apportées aux données dans un système en direct est un sujet distinct, tout comme la synchronisation des tables et des bases de données. La comparaison des documents XML est également hors de portée. Nous allons traiter uniquement de la comparaison de routine des données dans les tables
Je suis le plus susceptible d’utiliser des techniques TSQL pour comparer les tables lorsque:
Développement…
Au cours du développement d’une base de données, de nombreuses tables sont comparées. Ce n’est pas seulement le gros problème: Chaque fonction à valeur de table, par exemple, a besoin d’un harnais de test dans le script de construction qui s’assure qu’elle fait ce que vous pensez qu’elle devrait faire dans toutes les circonstances de test imaginables, et incorporant tous les cas de bord désagréables où elle a été capturée par les testeurs dans le passé. Chaque procédure stockée a besoin d’un test pour s’assurer que le processus qu’elle exécute fait exactement ce qui est prévu et rien d’autre.
Il fut un temps où l’activité de construction était plutôt tranquille, mais lorsque vous avez un test de construction et d’intégration nocturne, il est préférable de l’automatiser entièrement et de vous débarrasser de la corvée.
ETL
Lorsque vous automatisez le chargement de données dans un système, vous devez souvent tester diverses conditions. Avez-vous besoin de mettre à jour les versions existantes des lignes ainsi que d’insérer les nouvelles ? Avez-vous besoin d’un piège pour empêcher les entrées en double, ou même supprimer des entrées existantes?
Configuration des données de test.
Les scripts de cet article utilisent tous une table de la vénérable base de données PUBS. Nous allons utiliser la table des auteurs, mais augmenterons un peu le nombre de lignes à 5000 afin d’obtenir une taille un peu plus réaliste. J’ai fourni la source du tableau avec l’article.
I then created a copy of the table …
|
1
2
3
4
5
6
|
SELECT * INTO authorsCopy
FROM authors
GO
ALTER TABLE dbo.authorsCopy AJOUTE LA CONTRAINTE PK_authorsCopy LA CLÉ PRIMAIRE CLUSTERISÉE
(au_id) SUR LA CLÉ PRIMAIRE
GO
|
Puis modifie certaines des lignes.
|
1
2
3
4
5
|
METTRE À JOUR authorsCopy SET address=STUFF(address, 1, 1, »)
OÙ au_ID DANS (
SÉLECTIONNEZ LES 10 PREMIERS au_id
DE authorsCopy f
div> ORDRE PAR téléphone)
|
Donc maintenant, les deux tables devraient être principalement les mêmes avec quelques modifications mineures dans le champ d’adresse
Test pour voir si les tables sont différentes.
Parfois, vous voulez juste savoir si les tables sont les mêmes. Un exemple de ceci serait de vérifier qu’un TVF fonctionne correctement en comparant son résultat à celui d’une table existante avec les résultats corrects. La façon habituelle de le faire est avec le groupe de fonctions CHECKSUM() dans SQL Server, car elles sont très rapides.
En utilisant les sommes de contrôle
Vous pouvez utiliser la fonction BINARY_CHECKSUM pour vérifier si les tables sont les mêmes: enfin, à peu près les mêmes. C’est rapide, mais ce n’est pas parfait, comme je vais le démontrer dans un instant. Si vous avez une série de tests, par exemple, c’est généralement suffisant.
|
1
2
3
4
5
6
7
8
9
|
IF(
SÉLECTIONNEZ CHECKSUM_AGG( BINARY_CHECKSUM(*))
DES auteurs) =(
SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*))
FROM authorsCopy)
SELECT ‘ils sont probablement les mêmes’
ELSE
SELECT ‘ils sont différents’
|
Pour que cela fonctionne, votre table ne doit pas avoir TEXT, NTEXT, IMAGE or CURSOR (ou un SQL_VARIANT avec l’un de ces types) comme type de base. De nos jours, cela est de plus en plus rare, mais Si vous avez une sorte de complication, vous pouvez contraindre n’importe quelle colonne avec l’un des types non pris en charge dans un type pris en charge. En pratique, j’utilise généralement une routine qui vérifie les métadonnées et le fait automatiquement, mais ce n’est pas joli.
Dans une version de travail, vous voudrez probablement spécifier la liste des colonnes, surtout si vous devez faire une coercition explicite des types de données, ou si vous ne vérifiez que certaines colonnes,
Ni BINARY_CHECKSUM() ni sa sœur simple CHECKSUM() ne sont complètement exacts pour vous dire si quelque chose a changé dans une ligne ou une table. Nous montrerons cela en regardant les mots communs de la langue anglaise, contenus dans un tableau appelé CommonWords.. Vous vous attendez à ce qu’ils aient tous une somme de contrôle différente, mais ce n’est pas le cas.
|
1
2
3
4
5
6
7
8
9
|
SELECT string, BINARY_CHECKSUM(string) AS « Checksum »
ANS LE CAS D’UN GROUPE DE MOTS communs OÙ BINARY_CHECKSUM(chaîne) DANS
(
SÉLECTIONNEZ BINARY_CHECKSUM(chaîne)
À PARTIR DE mots communs
GROUPE PAR BINARY_CHECKSUM(chaîne)
AYANT COUNT(*) >2)
ORDRE PAR BINARY_CHECKSUM(string)
|
giving donnant le résultatArmed
Armés de cette information, nous pouvons rapidement démontrer que différentes chaînes peuvent avoir la même chose somme de contrôle
|
1
2
3
|
SÉLECTIONNEZ BINARY_CHECKSUM(‘reed le nerd’),
BINARY_CHECKSUM(‘a poursuivi le nid’),
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’)
|
….alors que whereas
|
1
2
3
|
SELECT BINARY_CHECKSUM(‘Cela ressemble beaucoup au suivant’),
BINARY_CHECKSUM(‘cela ressemble beaucoup au suivant’),
BINARY_CHECKSUM(‘Cela ressemble beaucoup au suivant’)
BINARY_CHECKSUM(‘Cela ressemble beaucoup au suivant’)
div> |
gives vous donne différentes sommes de contrôle comme celle-ci…
|
1
2
|
———– ———– ———–
-447523377 -447522865 -447654449
|
The sister function CHECKSUM() …
|
1
2
3
4
|
SELECT CHECKSUM(‘This looks very much comme la suivante’),
CHECKSUM(‘cela ressemble beaucoup à la suivante’),
CHECKSUM(‘Cela ressemble beaucoup à la suivante’)
|
finds les trouve tous les mêmes, car ils utilisent le classement actuel et mon le classement de la base de données est insensible à la casse. CHECKSUM() vise à trouver des chaînes égales en somme de contrôle si elles sont égales dans une comparaison de chaînes.
|
1
2
|
———– ———– ———–
-943581052 -943581052 -943581052
|
Donc, le mieux que vous puissiez dire est qu’il y a une forte probabilité que les tables soient les mêmes mais si vous devez être absolument certain, utilisez un autre algorithme.
Si cela ne vous dérange pas la différence dans les chaînes de texte, vous pouvez utiliser CHECKSUM() au lieu de BINARY_CHECKSUM()
La grande valeur de cette technique est qu’une fois que vous avez calculé la somme de contrôle dont vous avez besoin, vous pouvez la stocker sous forme de valeur dans la colonne d’une table au lieu d’avoir besoin de la table d’origine et donc vous pouvez faire tout le processus encore plus vite, et prenez moins de temps. Si vous stockez la valeur de somme de contrôle renvoyée par CHECKSUM() assurez-vous de vérifier par rapport à la table en direct avec une somme de contrôle générée avec le même classement.
Voici un exemple simple de la routine « ce qui a changé ».
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
wenous allons créer une table ‘somme de contrôle’ ‘à la volée’ en utilisant le SELECT INTO.
SELECT
au_ID,
BINARY_CHECKSUM(au_id, au_lname, au_fname, phone,,city,, zip,) AS
DANS auchk
FROM authorscopy
ORDER BY au_ID
/* maintenant, nous allons mettre une contrainte juste pour vérifier que nous n’avons pas gagné à la loterie (très peu probable mais pas complètement impossible que nous ayons deux lignes avec la même somme de contrôle) */
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. Petit Rocher DE 98949 1
|
Et puis on vient de ranger.
|
1
2
3
|
/* et nous revenons simplement à ce qu’il était, dans le cadre du démontage */
METTRE À JOUR authorscopy SET au_fname=’Arnold’
OÙ au_ID=’327-89-2366′
|
Bien sûr, vous pouvez utiliser un déclencheur mais parfois vous voudrez peut-être juste un rapport quotidien ou hebdomadaire des changements sans l’intrusion d’un déclencheur dans une table.
En utilisant XML
Une possibilité générale est de comparer la version XML des deux tables, car cela effectue la traduction du type de données en chaînes pour vous. Elle est plus lente que l’approche par somme de contrôle mais plus fiable.
|
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
SÉLECTIONNEZ ‘ils sont différents’
|
Ici, vous pouvez spécifier le type de comparaison en spécifiant le classement.
ou vous pouvez le faire en comparant les données dans les tableaux..
|
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)))
SÉLECTIONNEZ ‘ils sont à peu près les mêmes’
SINON
SÉLECTIONNEZ ‘ils sont différents’ SÉLECTIONNEZ ‘ils sont différents’
|
calculating en calculant une somme de contrôle de la version XML de la table. Cela vous permet de stocker la somme de contrôle de la table à laquelle vous comparez.
Trouver où se trouvent les différences dans une table
La tâche la plus simple consiste à déterminer où les tables ont un nombre de lignes identique et une structure de table identique. Parfois, vous voulez savoir quelles lignes sont différentes et lesquelles manquent. Vous devez, bien sûr, spécifier ce que vous entendez par « pareil », en particulier si les deux tables ont des colonnes différentes. La méthode que vous choisissez pour effectuer la comparaison est généralement déterminée par ces détails.
L’UNION ALL GROUPGROUP PAR technique
L’approche classique pour comparer les tableaux consiste à utiliser une instruction UNION ALL pour les instructions SELECT qui inclut les colonnes que vous souhaitez comparer, puis GROUP BY ces colonnes. Évidemment, pour que cela fonctionne, il doit y avoir une colonne avec des valeurs uniques dans GROUP BY, et la clé primaire est idéale pour cela. Aucune des tables n’est autorisée en double. S’ils ont un nombre de lignes différent, ceux-ci apparaîtront sous forme de différences.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SÉLECTIONNEZ au_ID DISTINCT
À PARTIR DU
(
SÉLECTIONNEZ au_ID
À PARTIR DU
(
SÉLECTIONNEZ au_id, au_lname, au_fname, téléphone, adresse, ville, état, code postal, contrat
DES auteurs
/div>
UNION TOUS
SÉLECTIONNEZ au_id, au_lname, au_fname, téléphone, adresse, ville, état, zip, contrat
À PARTIR DE authorsCopy)BothOfEm
GROUPE PAR au_id, au_lname, au_fname, téléphone, adresse, ville, état, zip, contrat
AYANT UN NOMBRE(*)<2) f
|
Si l’une des tables a un duplicata, cela vous donnera un faux résultat, comme ici, où vous avez deux tables très différentes et le résultat vous indique qu’elles sont les mêmes! Pour cette raison, il est judicieux d’inclure la ou les colonnes qui constituent la clé primaire et d’inclure les lignes une seule fois !
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
SÉLECTIONNEZ COUNT(*), Address_ID, TheAddress, ThePostCode
À PARTIR du
(
SÉLECTIONNEZ Address_ID, TheAddress, ThePostCode
DE
(
VALEURS
(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)
UNION TOUT
SÉLECTIONNEZ Address_ID, TheAddress, ThePostCode
DE
(
VALEURS
(8, « ‘The Pippins », 20 Gloucester Pl, Chirton Ward, Tyne &li; 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 ligne(s) affectée(s))
|
La technique peut être utilisée pour comparer plus de deux tables. Il vous suffit de UNION ALL les tables dont vous avez besoin pour comparer et modifier la clause HAVING pour filtrer uniquement les lignes qui ne sont pas dans toutes les tables.
En utilisant EXCEPT
Vous pouvez maintenant utiliser le EXCEPT beaucoup plus propre et légèrement plus rapide.
|
1
2
3
|
SELECT* from authors
EXCEPT
SELECT* from authorsCopy
|
Ceci montre toutes les lignes des auteurs qui ne se trouvent pas dans authorsCopy. Si elles sont identiques, cela ne retournerait aucune ligne
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
au_id au_lname au_fname adresse téléphonique ville état contrat zip
———– ———– ——— ———— ————————– ———– —– —– ——–
041-76-1076 Sosa Sonja 000-198-8753 29 Second Avenue Omaha CT 23243 0
187-42-2491 Mc Connell Trenton 0003090766 279 Hague Way 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 Green Milton Drive Norfolk WA 69949 1
697-84-0401 Montes Leanne 000-018-0454 441 East Oak Parkway San Antonio MD 38169 1
727-35-9948 Long Jonathon 000-8761152 280 Avenue Nobel Mouillage LA NULL 1
875-54-8676 Pierre Keisha 000-107-1947 763 Chemin Fabien Blanc Fremont ND 08520 0
884-64-5876 Keller Steven 000-2787554 45 White Nobel Boulevard Milwaukee NY 29108 1
886-75-9197 Ellis Marie 001032-5109 35 East Second Boulevard Chicago IL 32390 1
975-80-3567 Salazar Johanna 001-028-0716 17 New Boulevard Jackson ND 71625 0
(10 lignes affectées)
|
J’utilise uniquement SELECT* pour que les choses restent simples pour l’article. Vous détailleriez normalement toutes les colonnes que vous souhaitez comparer.
Cela ne fonctionnera que pour les tables avec le même nombre de lignes car, si les auteurs avaient des lignes supplémentaires, cela indiquerait toujours qu’elles étaient différentes car les lignes des auteurs qui n’étaient pas dans authorsCopy seraient renvoyées. En effet, EXCEPT renvoie toutes les valeurs distinctes de la requête à gauche de l’opérande EXCEPT qui ne sont pas également trouvées dans la requête à droite
Cela montre, espérons-le, ce que je veux dire
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
LES VALEURS
(9, ‘929 Augustine lane, Staple Hill Ward South Gloucestershire UK’, ‘BS16 4LL’),
(10, ’45 Bradfield road, Parwich Derbyshire UK’, ‘de6 1QN’)
) TableA (10, ’45 Bradfield road, Parwich Derbyshire UK’, ‘de6 1QN’)
Address_ID, TheAddress, ThePostCode)
SAUF
SÉLECTIONNEZ Address_ID, TheAddress, ThePostCode de
(VALEURS
(8, « ‘The Pippins », 20 Gloucester Pl, Chirton Ward, Tyne &Wear UK’, ‘NE29 7AD’ ),
(8, « ‘Les 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)
|
…yields …
|
1
2
3
4
|
Address_ID TheAddress ThePostCode
———– ———————————————- ———–
(0 ligne(s) affectée(s))
|
…alors …
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
td> |
SÉLECTIONNEZ Adresse, adresse, Code postal DE
(VALEURS
(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)
SAUF
SÉLECTIONNEZ Address_ID, TheAddress, ThePostCode
DE
(VALEURS
(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)
div>
|
..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)
|
Cette fonctionnalité de EXCEPT pourrait être utilisée à l’avantage si vous souhaitez particulièrement vérifier que TableA est contenue dans TableB. Ainsi, lorsque les tables ont un nombre différent de lignes, vous pouvez toujours les comparer.
Vous ne voudrez peut-être pas comparer toutes les colonnes. Vous devez toujours spécifier les colonnes que vous souhaitez comparer pour déterminer la « similitude ». Si vous souhaitez uniquement comparer l’adresse par exemple, vous utiliseriez …
|
1
2
3
|
SÉLECTIONNEZ l’adresse DES auteurs
SAUF
SÉLECTIONNEZ l’adresse DE authorsCopy
|
La technique de jointure externe
Il existe également la technique de la jointure externe. C’est une technique plus générale qui vous donne des installations supplémentaires. Si, par exemple, vous utilisez la jointure externe complète, vous pouvez obtenir les lignes inégalées dans l’une ou l’autre table. Cela vous donne une vue ‘avant » et « après » des modifications des données. Il est utilisé plus généralement en synchronisation pour vous indiquer quelles lignes supprimer, insérer et mettre à jour.
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.contrat
DES auteurs
JOINTURE EXTERNE GAUCHE authorsCopy
SUR authors.au_ID=AuthorsCopy.au_ID
ET authors.au_lname=authorsCopy.au_lname
ET authors.au_fname=authorsCopy.au_fname
ET authors.au_fname=authorsCopy.au_fname
ET authors.téléphone = authorsCopy.téléphone
ET COALESCE (auteurs.adresse, « ) = COALESCE(authorsCopy.adresse, « )
ET COALESCE (auteurs.city, « ) = COALESCE(authorsCopy.city, »)
ET COALESCE (auteurs.state, « ) = COALESCE(authorsCopy. »)
ET COALESCE (auteurs.zip, « ) = COALESCE(authorsCopy.zip, »)
ET auteurs.contrat = authorsCopy.contract
OÙ authorsCopy.au_ID EST NULL
|
Comme vous pouvez le voir, il y a des difficultés avec les colonnes nulles avec cette approche, mais elle est aussi rapide que les autres et cela vous donne un peu plus de polyvalence pour vos comparaisons.
Localiser les différences entre les tables
Vous aurez peut-être besoin d’un moyen rapide de voir quelle colonne et quelle ligne a changé. Une façon très ingénieuse de le faire a été publiée récemment. Il utilisait XML. ‘Comparer Les Tableaux Et Signaler Les Différences En Utilisant Xml Pour Faire Pivoter Les Données’ (ndlr: lien obsolète). C’est intelligent, mais trop lent. La même chose peut être faite purement en SQL. Fondamentalement, vous effectuez une comparaison colonne par colonne des données en fonction de la clé primaire, à l’aide d’une paire clé / valeur. Si vous faites la table entière à la fois, c’est plutôt lent: la meilleure astuce consiste à ne le faire que sur les lignes où vous savez qu’il y a une différence.
|
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
div> 58
59
60
61
62
63
64
|
INDIQUANT LA TABLE @temp(au_id VARCHAR(11) PRIMARY KEY) /* contient les clés primaires des lignes qui ont changé */
INSERT DANS @Temp(au_ID)determinedéterminez quelles lignes ont changé
SÉLECTIONNEZ au_ID
À PARTIR DE –utilisez la technique EXCEPT q qui est la plus rapide dans nos tests
(
SÉLECTIONNEZ au_id, au_lname, au_fname, phone,,city, state, zip,
DES auteurs
SAUF
SÉLECTIONNEZ au_id, au_lname, au_fname, téléphone, adresse, ville, état, zip, contrat
DE authorsCopy
) ffmaintenant, nous sélectionnons simplement les colonnes qui ont changé
SÉLECTIONNEZ lefthand.au_id,lefthand.name , à gauche. valeur COMME original, à droite.valeur modifiée
DE (nowmaintenant, nous présentons simplement les deux tables sous forme de paires de valeurs clés, en utilisant les versions de chaîne des données
SELECT authors.au_id, ‘au_lname’ COMME ‘name’, au_lname COMME ‘value’
FROM authors INNER JOIN @Temp altered ON altered.au_id=authors.au_id
UNION
SELECT authors.au_id,’ au_fname ‘COMME ‘nom’, au_fname COMME ‘valeur’
DES auteurs INNER JOIN@Temp altered ON altered.au_id=authors.au_id
UNION
SÉLECTIONNEZ les auteurs.au_id, ‘phone’, phone
DES auteurs INNER JOIN@Temp altered ON altered.au_id=authors.au_id
UNION
SELECT authors.au_id, ‘address’, address
DES auteurs INNER JOIN@Temp altered ON altered.au_id= authors.au_id
UNION
SELECT authors.au_id, ‘City’ AS ‘name’, City AS ‘value’
FROM authors INNER JOIN@Temp altered ON altered. au_id =authors.au_id
UNION
SÉLECTIONNEZ les auteurs.au_id, ‘State’, state
À PARTIR DES auteurs INNER JOIN @Temp altered ON altered.au_id=authors.au_id
UNION
SÉLECTIONNEZ les auteurs.au_id, ‘zip’, zip
DES auteurs INNER JOIN @Temp modifié SUR altered.au_id=authors.au_id
UNION
SÉLECTIONNEZ les auteurs.au_id, ‘contract’, CONVERT(CHAR(1), contract)
DES auteurs INNER JOIN @Temp modifié SUR altered.au_id=authors.au_id) LEFT
INNER JOIN(
SÉLECTIONNEZ authorsCopy.au_id, ‘au_lname’ COMME ‘nom’, au_lname COMME ‘valeur’
DE authorsCopy INNER JOIN @Temp altered ON altered.au_id =authorsCopy.au_id
UNION
SÉLECTIONNEZ authorsCopy.au_id, ‘au_fname’, au_fname
DE authorsCopy INNER JOIN @Temp modifié SUR altered.au_id =authorsCopy.au_id
UNION
SÉLECTIONNEZ authorsCopy.au_id, ‘phone’, phone
DE authorsCopy INNER JOIN @Temp modifié SUR altered.au_id =authorsCopy.au_id
UNION
> SÉLECTIONNEZ authorsCopy.au_id, ‘address’, address
DE authorsCopy INNER JOIN @Temp altered ON altered.au_id=authorsCopy.au_id
UNION
SÉLECTIONNEZ authorsCopy.au_id, ‘City’ AS’name’, City AS’value’
DE authorsCopy INNER JOIN @Temp altered ON altered.au_id =authorsCopy.au_id
UNION
SÉLECTIONNEZ authorsCopy.au_id, ‘State’, state
DE authorsCopy INNER JOIN @Temp altered ON altered.au_id =authorsCopy.au_id
UNION
div>
SÉLECTIONNEZ authorsCopy.au_id, ‘zip’, zip
DE authorsCopy INNER JOIN @Temp modifié SUR altered.au_id =authorsCopy.au_id
UNION
SÉLECTIONNEZ authorsCopy.au_id, ‘contract’, CONVERT(CHAR(1), contract)
DE authorsCopy INNER JOIN @Temp altered SUR altered.au_id=authorsCopy.au_id) Droitier
SUR gaucher.au_ID=droitier.au_ID
ET lefthand.name=righthand.name
OÙ à gauche.valeur <> à droite.valeur
|
dans notre exemple, cela donnerait:
|
1
2
3
4
5
6
7
8
9
10
11
12
|
nom de l’id_au original modifié
———– ——– —————————- ————————————
041-76-1076 adresse 29 Second Avenue 9 Second Avenue
187-42-2491 adresse 279 Hague Way 79 Hague Way
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 New Boulevard 7 New Boulevard
|
Cette technique fait pivoter les lignes des tables qui présentent des différences dans une table d’entité-attribut-valeur (EAV) afin que les différences dans une ligne puissent être comparées et affichées. Il effectue cette rotation en UNION dans le nom et la valeur de chaîne de chaque colonne. Cette technique fonctionne mieux là où il n’y a pas un grand nombre de différences.
Conclusions
Il n’existe pas de méthode idéale unique pour comparer les données dans des tableaux ou des résultats. L’une des nombreuses techniques sera la plus pertinente pour une tâche particulière. Tout dépend précisément des réponses dont vous avez besoin et du type de tâche. Avez-vous besoin d’une vérification rapide qu’une table n’a pas changé, ou avez-vous besoin de savoir précisément quels sont les changements? SQL est naturellement rapide à effectuer cette tâche et les comparaisons de tables et de résultats sont une tâche familière à de nombreux développeurs de bases de données.
S’il existe une règle générale, je dirais que le travail exploratoire ou ad hoc nécessite un outil tel que la comparaison de données SQL, alors qu’un processus de routine dans la base de données nécessite une technique SQL découpée à la main.