Source: MS Access on Windows network share
Target: MySQL/MariaDB on Ubuntu
Tools: mdb-export, mysqlimport
record count: 1,5 Mio +
I wonder if there is a fast and reliable way of comparing the imported data records.
Is there an SQL standard equivalent to e.g. md5 fingerprint hashes of files? Right now, I am building different import routines and I only want to fast check for similarity and (if failed) search for the detailed differences later on.
3
Answers
I found it.
It's very simple and very fast.
Will give you a number value to compare.
And it's Transact-SQL so will hopefully work the same on MS Access, MySQL and MariaDB
A somewhat of a quick-and-dirty approach for individual columns can be implemented using stored aggregate functions which should be SQL standard.
This is how you’d do it with MariaDB:
You can then calculate a checksum from of a column as such:
This of course relies on the SHA1 of the column being the same on all databases. Conversions into strings should make it mostly compatible but there might be differences in how these are implemented in different databases.
The percona toolkit has the tool you need.
https://docs.percona.com/percona-toolkit/
See
pt-table-checksum
andpt-table-sync