Data compare and sync

Data compare pairs the rows of matching tables, shows which rows are new, missing or changed, and runs a script that makes the target’s rows match the source.

Create a data project

A data project pairs two databases whose rows you compare. You need at least one connection.

1. Open Data compare and choose New data project.
2. Enter a Name, such as Reference data sync.
3. Choose the source (X) and target (Y): a Connection, a Database and, except on MySQL, a Schema on each side.
4. Choose Create data project.

To change it later, choose More > Edit on its row.

Choose the tables

1. Choose Compare on the data project.
2. In Choose tables, tick the tables to compare. Every table that can be compared is ticked.
3. Choose Compare data.

Tables that exist on both sides are compared row by row, matched on their key columns. Each row shows the table, the columns it is Matched on, and badges for any options set on it.

A table without a tick box cannot be compared yet:

• no target table: the target has no table of its name. Compare it with another table through its options.
• needs attention: the reason shows under its name, for example a table with no primary key or unique key. Fix it in the table’s options.

Under the list, “Not compared in the target” names target tables that no source table is compared with.

Table options

Choose Table options on a table’s row.

Compare with

The target table to compare with: Same name by default, or any other target table. A target table already compared with another source table cannot be chosen.

Columns

Each source column is compared with the target column of its name. Choose another target column to map columns of other names, or Don’t compare to leave a column out. A warning icon marks columns whose kinds of value differ.

Compare rows by

How rows are paired:

• The table’s key: its primary key or unique key.
• These columns: columns you choose, for a table without a key or to pair by something else.

Choose Check key to count each side’s rows and check the key. It reports rows that share a key value or have a blank key, such as “2 rows share 1 key value: not unique”.

Filter rows

Enter a WHERE condition to compare and sync only some rows. Untick Same filter on both sides to give each side its own filter. If the filters differ, make sure they choose the same rows.

Choose Test filter to count each side’s rows, or see the database’s error. A filter the server refuses, such as one with a second statement, cannot be saved.

Save or reset

Choose Save. If something stops it, the dialog says what: for example no key, or a target column compared with two source columns.

Choose Reset to automatic to remove the table’s options.

Results

The results open when the comparison finishes. The totals count the rows different, only in source, only in target and identical across all tables. Below, each table shows its own counts and status.

Choose a table to see its rows. Choose the kind of rows to show:

• Different: changed rows, each changed cell as “old → new”. Hover over a cell to see the target’s value.
• Only in source and Only in target: the rows on one side only.
• Identical: counted but not stored, so not listed.

For changed rows, each column header counts the rows that differ in it. Tick Changed columns only to hide the columns with no changed cell. The key columns stay.

NULL shows as NULL. A value over 300 characters shows its start and how many characters more.

A table that could not be compared shows its error. The other tables still have their results.

Earlier runs

Each comparison is kept as a run. Choose a run in the header to open it. Compare again opens Choose tables for a new run. Delete result deletes the run shown.

Sync

The Sync script panel under the results writes and runs the script that makes one side’s rows match.

1. Choose the direction: Update the target (Y) or Update the source (X).

2. Set the options:

   Option	Default	What it does
   Switch foreign keys off	On	No cascades run, and tables change in any order. The keys go back on before the commit and every row is checked: a row without its parent row stops the script.
   Switch triggers off	Off	Triggers on the synced tables do not fire for these changes. Not available on MySQL, which cannot switch triggers off; there they fire for every changed row.

3. Choose Generate script. Parallax shows how many inserts, updates and deletes it holds, and the SQL, which runs in one transaction. Choose Copy SQL to copy it.

4. Choose Run on <connection>, then Run script in the confirmation.

The run button is red when the script deletes rows. If the script fails, nothing is changed.

Changing the direction or an option discards the script; generate it again.