I'm trying to figure out why the numbers in one of our long standing reports shifted unexpectedly last Friday. So far, nothing we can find points us to the culprit.
Our test system is holding the copy of the database from the day before the problem popped up so in theory I should be able to compare some of the tables from the test system to their counterparts in the live system and come up with some likely suspects.
So the question is this. Is there a straightforward way to do a differential on two tables that are structurally the same? (other than writing a bunch of SQL from scratch) I'd like to see both changes in existing records and any rows that were added. I can get the additional rows pretty easily through the primary keys, but seeing what changes may have happened to existing records might be trickier.
To answer my own question a bit. It looks like the EXCEPT and INTERSECT commands in SQL will do alot of this. I'd still be interested in something that is a little more visual. Something like WinDiff, but for the database.
There's also this timely post from a SQLServerCentral daily that I received this morning:
Comparing data between tables seems like a simple thing to do, but it can eat up a lot of a SQL Server professional's time. Stephen Tirone shows how multiple assignment variables can be used to make this task a little easier. More »
Thank you all for the information! I hacked through my problem in a more mundane fashion, but its always cool to learn new stuff. Both Redgate and the multiple assignment variables look intriguing. I'll keep both in mind when future problems arise.