May
11
Written by:
Jeff Smith
5/11/2010
Today I want to talk about something that should be beneficial to any person who has ever needed to connect to an Oracle database, comparing data. By that I mean, taking two sets of data and examining them for
- Rows that are common between
- Rows that have common ID fields but different detail values
- Rows that are missing from set A or Set B.
I believe it’s safe to say that anyone that works with the database has had to perform this exercise. Whether you’re performing QA after a migration or ETL project, or just simply need to compare the data between 2 different versions of a query you are working on, being able to process the massive amounts of data can be challenging.
I don’t believe it’s a coincidence that one of the most popular Toad features revolves around exporting data to an MS Excel spreadsheet. I’ve worked with many folks who do this just to get help comparing data sets. Of course, SQL gives us the MINUS set operator that allows one to identify records that are not part of one set or another.
“Not that there’s anything wrong with that.”
Toad for Oracle’s ‘Database – Compare – Compare Data’ makes a MINUS Query a bit easier to execute, your data may not be in the same database, not to mention the convenience factor of DB_LINKs!
I don’t believe either of these mechanisms is ideal. Eye-balling data encourages human error, and using SQL to process the data can be time-intensive, not to mention inaccessible to folks who are not adept at SQL.
I am very comfortable in saying that one of the most profound developments in the last 3 years in the Toad domain is the introduction of Toad for Data Analysts. I say this because it’s ability to compare data with mouse-click is so compelling. Additionally being able to dynamically generate INSERT|UPDATE|DELETE scripts to address these differences takes this feature to the next level.
Before I take you through the feature, let’s address who is eligible to use this feature.
Availability
Quest makes Toad for Data Analysts available to all licensed users for Toad for Oracle current on their maintenance agreements. The software is bundled with Toad for Oracle or can be downloaded separately.
Toad for Data Analysts is also available for purchase as a separate stand-alone product.
The Basics
- Two methods for doing a compare
- Compare table(s) to table(s)
- Data can come from different databases or even Excel
- Can compare all the data or subset using WHERE filters
- Tables do not need common names (nor their columns)
- Compare result-set from two SQL queries
- The data is processed client-side
- You can compare data across databases or even RDBMS platforms
Comparing Data from Different SQL Statement Executions
From any data grid in the Editor or Query Builder, a user can simply mouse-right-click and say ‘Compare To’.
Assuming there is a common ID or UNIQUE field between the two sets, Toad can show said differences side by side.
Clicking in a row in one set will result in Toad attempting to find the corresponding row in the other set. Missing rows are shown as empty in the opposing set. Cells with different values are highlighted as well. The report can additionally be printed.
Extremely helpful for evaluating result sets as you experiment with your query, such as different JOIN conditions.
Comparing Data between Tables
If you need to compare data between two different sources, Toad for Data Analysts provides an extremely straightforward wizard to accomplish your task.
Step 1: Tell us where the data is
Step 2: Confirm keys and columns to be compared. Optionally supply a WHERE filtering clause for either data set.
Step N: View differences, select rows to be Synched (yes I skipped a few screens!)
Two Parting Tricks
- Use ‘File – Save’ to store your compare wizard session. You can then use ‘File – Open’ to ‘refresh’ your compare going forward. This will save you a TON of time.
- You can actually compare as many pairs of tables as you want. For example you could compare 10 Access tables to 10 Oracle tables in a single swoop.