Compare and Sync Multiple Tables with Toad

Available starting in Toad 12.11.0.40.  Toad Expert Edition or DB Admin Module is required

For a long time, Toad has had a window that lets you compare table/view/mview data, and Sync it if you have the Toad Professional Edition or DB Admin Module.  That's fine and good, but if you have lots of tables to compare, you have to set it up for each table, and run each table individually.   Wouldn't it be nice if you could just point Toad at a couple of schemas and have in sync all (or some) of the data in the tables of those two schemas?   Well, now you can!

To get started, go here…

.

…or here…

..and you'll see this

I've made this window as simple as I can.  

The "Syncronization" group box is here on the first tab, next to the schema selection, so you won't have to go to any of the other tabs to see what will happen when you click the green triangle to run it.   We're going to synchronize the data in your tables (or at least create a script to do that).   There is not an option to review the differences first.   If you want that, then go Database -> Compare -> Data.

To sync data in all (compatible) tables in a pair of schemas, all you really need to do is pick the schemas and click the green triangle at the top.  There are some options on the other tabs, but they default to what I think are the most useful settings.

Before we just blaze on though, here are some things that you should know.  There are two different techniques of comparison that this window can perform.  I've optimized both of them as much as I can, but one is much faster than the other.   And which technique Toad will use all hinges on what you do on this first tab.  So here's what you want to do:

• Fastest – Use the same connection to access the source and target schemas if you can.  Obviously, this will only be possible if they are in the same database.  In this case, Toad can sync the tables with just two SQL statements per table per table – a DELETE and a MERGE.  All the work is done by the database, so the data won’t won’t be fetched to your client PC, and you won’t run out of memory syncing large tables. 
• Almost as fast – If your schemas are in different databases, check "Allow Toad to create a temporary DB Link from Target to Source".  In this case, Toad creates a private DB Link on the target database which points back to the source.  This will allow Toad to join the tables and perform the same two-SQL sync operation as if the tables were on the same database.  The DB Link will have full TNS info, so you don’t need to be concerned with a TNSNames.ora entry existing in the target database server’s TNSNames.ora file.   Toad will drop the DB Link when sync is complete.
• Slower – If you can’t allow a DB Link to be created, and your schemas are in different databases, then Toad has no choice but to fetch all rows from each table to your PC and compare them itself.   This is the slowest and most memory-demanding option.  It’s much better to use same connections or DB Links.
• Here, the "Array DML" option comes into play.  Array DML means that Toad will apply deletes in batches of up to 1000 rows at a time, and inserts/updates in batches of up to 100 rows at a time.   It makes a BIG difference in terms of speed.  Leave it checked.   The only downside to this is that update statements will have to update every column in the rows being updated, even if only one column value actually changed.   If you have some objection to that, then you can uncheck Array DML. 
• Having "Array DML" unchecked is slow, but writing the SQL statements to a script and then running the script is slowest option

I've named the Same-Connection-or-through-DB-Link technique "Comparison by SQL", and the Fetch-all-data-to-your-PC technique "Row by Row Comparison".  Remember that, because I'll refer back to them by these names later, and you'll see it in the GUI too.

As a point of reference, in my tests, comparing and syncing a 1 million row copy of DBA_OBJECTS – 

In the same connection, it takes only a few seconds.

In different connections and across a DB Link, it takes about 10-15 seconds.

In different connections without a DB Link, using Array DML, it takes about 8 minutes and 550MB RAM.

In different connections without a DB Link, and NOT using Array DML – it really depends on how many rows need to be synchronized.   If everything matches, it'll goes as fast as Array DML.   If there percentage of rows that need to be synchronized is high, then it might take up to 30 minutes for a table this size.  

Every environment is different, so your compare and sync times will not be the same as mine.

Next tab.  Probably Optional.

To compare all tables with matching names in your schema pair, do nothing on this tab.  "Match tables automatically" and "Table names match exactly" are checked by default, which is what you want.

You only need to do something here if your table names don't match exactly, or if you only want to compare some of the tables.  Hopefully this looks familiar – I copied it from Toad’s “Compare Multiple Schemas” window.

So here's what the manual table matching looks like:

To see a list of tables to be compared (even if it's set to automatic), click "Load".    If you want to match them up manually (maybe their names don't match), you can use the dropdown in the "Target" column as shown above.   To check their compatibility for compare and sync, click "Validate".   There's a list at the end of this blog detailing what might disqualify a table for compare and sync.

Options tab.  Optional.  (hence the name)

I think these are all pretty self explanatory so I'm not going to comment on every one of them, but I do have a few comments.  

• Commits are automatic, but you do have some control over them with the "If an error occurs during sync" option.  If you choose "Rollback current table and continue", then Toad commits after each table (unless there is an error, then it rolls back just that one table).  If you choose "Rollback all tables and stop", then Toad either commits at the end of the process or rolls back when the first error happens.  
• There is no "commit/don't commit" option because with DDL happening at the beginning and end of each compare and sync (disable/enable of constraints and possibly triggers, as well as create/drop DB Link), commits would be implicit in most cases anyway.

Last tab

When you click the green triangle to run the compare and sync, you'll automatically be taken here.  There's a row for each table to be compared, and Toad will make two passes through the list.  The first to make sure tables are compatible and disable foreign keys, and the second pass to actually do the compare and sync.   The screen shot above shows a "Comparison by SQL" run.   The compare and sync is done in background queries, and we display how many rows are deleted and merged (merge includes both insert and update, and we don't have details about how many rows of each).

"Row By Row" comparison

Row By Row comparisons have details about rows inserted, deleted, updated, and number of matching rows, which are updated once per second as the compare/sync progresses.  .  There is also an "estimated time to completion" countdown on each step (one step being deletes, another being insert and update).

Finally, here's a chart showing various conditions that may cause a table to be incompatible for comparison:

(Potential) Problem	Supported in Row By Row Comparisons	Supported in Comparison by SQL
LONG, LONG Raw datatype	Yes	No
User-defined datatypes	No	No
Nested tables and arrays	No	No
BFILE datatype	No	No
CLOB, NCLOB, BLOB, XMLTYPE	No	Yes, if not using DB Link
All other datatypes	Yes	Yes
Columns in diferent order	Yes	Yes
Missing Primary Key	No	Yes
Different Primary Keys	No	No
Identity Columns	No	No
Extra columns in source	Optionally, Yes	Optionally, Yes
Extra columns in target	Optionally, Yes	Optionally, Yes

Edit:  In Toad 12.12, the look and feel of this window is improved, and Identity columns are no longer a problem for the compare/sync.   These columns will just be ignored since they can't be updated.