I am testing an application that is migrating to a new schema. I have to ensure that data that was in the previous schema is available in the new one. I've found tools that allow me to compare table/table and view/view, such as SQL Data Compare, but I haven't found anything that will allow me to create a data set that contains pairs of queries and compare the results. I will ultimately have 600-700 queries to validate all of the data.
For example, I would like to compare the output of the following queries (both in SQL Server):
<font class="small">Code:</font><hr /><pre>SELECT a, b, c
FROM table1, table2
WHERE table1.type= 'some value'
and table1.Id = table2.Id;
select x, y, z from table_or_view_in_new_schema;
Does anyone have a suggestion of tools I should check out?
And maybe I'm making this too simplistic. But, I would prefer to compare the data directly rather than attempting to compare results of queries. The two biggest assumptions in this approach are that you can query across multiple databases (and that your environment can support it) and that you have some sort of source-to-target mapping that details how data is being moved from one database to the next.