Table comparison between two Oracle databases

[codehead]

Hello,

I am trying to compare the same table on both our development and production databases to find the differences. I can connect to both databases with Access 97 and I have linked to the tables in it (they are too large to import), but I can't seem to figure out how to compare each field for differences. I suspect that I need to write some VBA code, but I need an example. Or I could create a small Java program, but again I need an example to use as a starting point. I asked my DBA to link the two tables, but he refuses to do it because of the administration headaches. Has anyone done this before? Can anyone point me to an example/reference?

Thanks!

 

[zerosandones]

One approach which I've used before is to run three separate SQL statements to cover the three different scenarios you need to address. NOTE: this type of table comparison must assume that each table structure is identical to each other and that each table has the same primary key or unique index to act as a common reference point:

1) Determine which records exist in Table1 which do not exist in Table2 i.e. which primary key or unique index value is in Table1 and not in Table2:

SELECT Table1.*
FROM Table1 LEFT JOIN Table2 ON Table1.PrimaryKey = Table2.PrimaryKey
WHERE Table2.PrimaryKey Is Null;

2) Determine which records exist in Table2 which do not exist in Table1 i.e. which primary key or unique index value is in Table2 and not in Table1:

SELECT Table2.*
FROM Table2 LEFT JOIN Table1 ON Table2.PrimaryKey = Table1.PrimaryKey
WHERE Table1.PrimaryKey Is Null;

3) Which records having the same primary key or unique index between tables have any data field value different than the other (Null values are processed by converting any null value to a descriptive "No Value" text string for criteria and output):

SELECT nz([Table1]![Field1],"No Value&quot AS Table1_Field1, nz([Table2]![Field1],"No Value&quot AS Table2_Field1, nz([Table1]![Field2],"No Value&quot AS Table1_Field2, nz([Table2]![Field2],"No Value&quot AS Table2_Field2
FROM Table1 INNER JOIN Table2 ON Table1.PrimaryKey = Table2.PrimaryKey
WHERE (((nz([Table1]![Field1],&quot;No Value&quot)<>nz([Table2]![Field1],&quot;No Value&quot)) OR (((nz([Table1]![Field2],&quot;No Value&quot)<>nz([Table2]![Field2],&quot;No Value&quot));

Hope this helps,

00001111s