Comparing Nearly Identical Database Tables

Databases
1

Suppose you have a database table named gw_geog which you’ve published online. So you actually have two copies of gw_geog - local and online.

Can anyone recommend a method for comparing these two tables? Let’s say they each have 50,000 rows and they’re absolutely identical, except for one field in one row that was changed in one table. Imagine a single cell which contains “washington” in one version and “Washington” (with a capital W) in the other.

I just wondered if there’s a (hopefully not too technical) method for finding tiny differences between tables.

Thanks.

2

You can use like this -

SELECT * FROM (SELECT Id, Col1 FROM t1, ‘old’
UNION ALL
SELECT Id, Col1 FROM t2, ‘new’) t
ORDER BY Id

3

Since you didn’t specifically mention which database product you use, here are the links to Red Gate’s SQL Data Compare.

http://www.red-gate.com/products/mysql/mysql-data-compare/

We use both the Data Compare and the Schema Compare products where I work, and I must say, they do pretty well. There are a few features I wish they had, but they get the job done.

4

Thanks for the tips; I’ll check them out. (I’m using MySQL.)