August 7, 2007 at 3:20 pm
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:
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?
Regards,
glen accardo
August 7, 2007 at 3:42 pm
I don't know of any tool that will do that. you could create 2 side databases - one for old schema and one for new. then do inserts or select into new tables in those databases and then do a data compare. Not sure how deep you are in creating your test queries, but this is the only solution I could think of.
August 8, 2007 at 6:07 pm
Don't the compare tools from ApexSQL and RedGate compare views as well? If so, you could turn your queries into views and compare them. All of these views could actually be in a separate database so you don't affect your schema.
August 10, 2007 at 2:31 am
Hi Glen,
in SQL Data Compare you can customize your comparison. You can specify the columns that you want to include in the comparison, and you can specify a where clause to filter the rows as well. If you need to compare the result of a select statement that involves multiple tables, then as Chad suggests, using views is a possible solution.
Regards,
Andras
August 10, 2007 at 8:34 am
The posts from Chad and Adam are exactly correct. It is very easy to create a two databases, each with a different set of views. SQL Data Compare then goes through this very easily and gives me the exact results I need.
Thanks for the help,
glen accardo
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply