An Easy Way to Sync Data Without Sinking in Queries

We have all been there. We start making changes to data inside a table either manually or by running one or a series of queries, only to find out that we messed part of it up. We know we have our original, so all hope is not lost. Yes, we could just go back to square one and start fresh, but sometimes going back to square one is more time-consuming than simply fixing the issue itself.

Decision made. No problem, we will just write some queries to fix the errors and we are back in the game. We start to think of ways to write queries to fix the mistakes, but that quickly escalates to a complex query or two, only to realize we still need a way to verify that we did, in fact, catch everything. Not to mention, we will also have to do a compare.

Well, why bother with all that work when you have Toad for Oracle? The Compare Data tool easily compares two tables and produces results that quickly show everything from the data that exists in one or the other table, to the differences within the rows that exist in both tables. Plus, the Compare Data tool will even sync the data for you at the mere push of a button!

Here are the simple steps:

Open the Compare Data tool: Database > Compare > Data

Next, enter the information on our “target” and “source”.

NOTE: A database link is required for tables that exist in a different database.

 The Options tab allows for several options such as Sort Area Size and enabling Optimizer Hints.

Before continuing, take note of the Optional Where Clause. This option empowers the user to limit or set conditional statements during the comparison.

On the next screen, the user will be able to select the columns they would like to compare. 

Select all columns that are equal on both tables. This will create a join between both tables and enables the comparison to be made. 

The final step is to hit the Compare Button.

It is as simple as that, and the comparison is generated.

Row Counts –“Matching” if the row count of both tables match and “Differ” if they don’t match.

Source Only – rows that are ONLY found in the source table.

Target Only – rows that are only found in the target table.

Differences – rows that are different.

NOTE: The source rows are shaded grey, target rows are white and the differences are shaded yellow.

The Show Query  button on each screen above will allow the user to view the query being executed. This query can be modified if changes are needed.

NOTE: The Execute  button allows the user to run the query if modifications were made. The  button allows the user to delete/insert the given rows to the target table, or update the target table using the data found in the source table. Our recommendation is to carefully review the action being taken before proceeding. The user is able to view the respective query for the action (delete, insert or update) being taken under the Synchronization tab as described in the next topic. 

Synchronization – Allows the user to delete, insert, or update the given rows to the target table from the source.

NOTE: The  button WILL take actions selected under the Statement Types. Our recommendation is to carefully review the action being taken before proceeding. The user is able to view and review the respective query for the action (delete, insert or update) selected by using either using the “Send to Editor”  button or “Save”  button.

Here is a sample of query that is generated when Send to Editor is selected.

As shown above, in just a few simple steps we are able to compare and sync our data and move forward with ease, knowing that we have cleaned up our issue.

For other features of Toad for Oracle or to download a trial of Toad for Oracle, please visit Toad World.  

In my next blog, I will show you how to compare data from tables that contain CLOBs or in instances where the column names within the two tables do not match but are the same.