Automate data compare of queries executed against two different databases

Toad Data Point
1

Hello,

We are exploring Toad Data Point for enterprise data project, which involves comparing target tables that reside in MySQL database. Data for each of these tables are sourced by joining multiple tables in MS SQL Server database. To compare the enrichment process of target tables, we are using TOAD Data Point to run the source and target queries in separate Query Editor windows and then use the “Compare To” option of the result set to do the actual data comparison. It works very well for us so far, but given the manual nature of it we would like to automate it. The “Compare Data” within the automation wizard expects us to provide a data compare file (*.dcp), which only allows table to table comparison. Is there a way to compare two SQL queries executed against two separate database and compare the results through automation wizard? Please let us know.

Thanks,

Guru

2

Have you tried exporting the results to a csv file and compare the files?

3

We have not tried the csv option. Any pointers on how to do it through automation wizard will be helpful.

4

Use automation.

  1. Run the query against the first database and save it in CSV.

  2. Run the query against the second database and save it in CSV.

  3. Use the data compare in automation, select the source and target as CSV and map the fields.

  4. Automation has ton of feature like (delete the files, create new ones etc)

Let me know how that goes.

On Mon, Oct 30, 2017 at 5:27 PM Guru Krishnan bounce-Guru_Krishnan@toadworld.com wrote:

RE: Automate data compare of queries executed against two different databases

Reply by Guru Krishnan
We have not tried the csv option. Any pointers on how to do it through automation wizard will be helpful.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or Unsubscribe from Toad Data Point Forum notifications altogether.

Toad Data Point - Discussion Forum

Flag this post as spam/abuse.

5

The “guess data type” does not seem to work well - is there any way to override the data type for CSV files ?

6

The assignment of datatypes is done by the Microsoft driver. It takes a sampling of data to determine the datatype and is not always correct.

We do have an outstanding request to add support for queries to be used in Data Compare but we have not been able to get to this.

The export idea to csv idea above is a good one however, if you have Pro I would suggest you export your query results to local storage. Or is you only have Base and the results are not too large you can export to Access and then user Data Compare in Automation