We are migrating our database to a new schema but want to validate the data has been moved correctly.
Traditional data comparison tools can compare two databases for differences if the schemas are the same. In our case there have been changes to the table designs but all the data from the old schema is in the new one, it's just been moved around a bit, and I need to make sure it is correct. We have tens of millions of rows so manual inspection is not an option.
Are there any tools which could aid in this type of comparison?
If not are there any libraries/frameworks which could help kick start the development of a custom solution?
I'm happy to use a database specific solution if necessary, in this case for SQL Server 2008.
My soluton: I'm comparing the two data sets by creating a
VIEW of each table on the old database with the same fields as the new database table.
I then compare the data using the technique described here: The shortest, fastest, and easiest way to compare two tables in SQL Server: UNION !
I'm lucky in this migration as the overall table structure is similar to the old database, with fields moved from one table to another, dropped or added. In the case of the dropped and added there is nothing to compare, for the fields which have moved or been aggregated I do calculations in the view to provide the correct information for comparison.
UNION comparison shows me only the rows with differences, so as soon as the data is correct I get an empty result set.