Compare Tables Between Databases

**Maks Romih** · Nov 13 '05, 02:43 AM

Re: Compare Tables Between Databases

This is quite common problem of comparing two databases, not only when
they should be equal but also when they are slightly different, for
example, when you replicate some big database.

If you have primary or unique keys in all the tables, then you can
construct the SQL queries to show the differences.

First you link the tables of both databases into some Access MDB. For
example, with the name A_orig you link to table A in original Access
MDB, while with the name A_copy you link to your MySQL copy of the
table A over ODBC. Suppose that the table A has two-column unique key
on c1, c2 and some other columns c3, c4, ...

Then you can construct three queries, one for the diffenreces in
fields for the rows that exist in both tables, one to show the rows
that exist only in original table and one to show the rows that exist
only in copy.

First query would look like:

SELECT O.c1, O.c2, O.c3, O.c4, ..., C.c3, C.c4, ...
FROM A_orig O INNER JOIN A_copy C ON O.c1 = C.c1 AND O.c2 = C.c2
WHERE O.c3 <> C.c3 OR O.c4 <> C.c4 OR ...

Second query would look like:

SELECT O.c1, O.c2, O.c3, O.c4, ..., C.c3, C.c4, ...
FROM A_orig O LEFT JOIN A_copy C ON O.c1 = C.c1 AND O.c2 = C.c2
WHERE C.c1 IS NULL;

Third query would look like:

SELECT O.c1, O.c2, O.c3, O.c4, ..., C.c3, C.c4, ...
FROM A_orig O RIGHT JOIN A_copy C ON O.c1 = C.c1 AND O.c2 = C.c2
WHERE O.c1 IS NULL;

These three queries you can put into a union query and make
an Access report on it, if you want.

Considering that you have quite a lot of tables, you can write
the generating procedure, to automatically create all the above
queries for all the 120 tables. The sketch of the generating
code would be like this:

Sub Gen_compare()
Dim db As Database
Set db = CodeDb()
Dim td As TableDef
Set td = db.TableDefs
Dim ix As Index
Dim f As Field
For Each td In db.TableDefs
If td.Name Like "*_orig" Then
For Each ix In td.Indexes
If ix.Primary Then Exit For
Next i
Dim sName_root As String 'the initial part of the table name,
' without _orig

sName_root = Left(td.Name, Len(td.Name) - Len("_orig"))
Dim sSelect As String
Dim sFrom As String
Dim sWhere As String
sSelect = "SELECT"
sFrom = "FROM " & sName_root & "_orig O INNER JOIN " _
& sName_root & "_copy C ON"
sWhere = "WHERE"

Dim n As Integer 'to know when delimiter

n = 0
For Each f In ix.Fields
n = n + 1
If n > 1 Then
sFrom = sFrom & " AND"
End If
sFrom = sFrom & " O." & f.Name & " = C." & f.Name
Next f

n = 0
For Each f In td.Fields
n = n + 1
If n > 1 Then
sSelect = sSelect & " ,"
sWhere = sWhere & " OR"
End If
sSelect = sSelect & " O." & f.Name & ", C." & f.Name
sWhere = sWhere & " O." & f.Name & " <> C." & f.Name
Next f

End If
Dim qd As QueryDef
Set qd = db.CreateQueryD ef(sName_root & "_inner", _
sSelect & vbCrLf & sFrom & vbCrLf & sWhere)
db.QueryDefs.Ap pend qd
db.QueryDefs.Re fresh
Next td
End Sub

Maks Romih

Prakash RudraRaju <prakash@ece.ar izona.edu> writes:
[color=blue]
> Hi,
>
> I have recently migrated MSAccess Tables (nearly 120) to MySQL. To
> test successful migration I want to compare all tables. I have linked
> MySQL tables through ODBC connection.
>
> I want to create a report that compares all records between all tables
> in both databases. I am looking for a report with differences in
> number of
> records and differences in fields if 2 records are
> different. Probabaly it can be acheived through query, but I couldn't
> find any help on queried between two databases.
>
> Thanks for your help. Any pointers to help me create a report in MS
> Access will be greatful.
>
> Thanks,
> Prakash.[/color]

Compare Tables Between Databases

Compare Tables Between Databases

Comment