Data Compare Results


Data compare displays a list of tables and views if included and the number of rows that are different in each table. The differences are shown by type and the Sort or Index column is used to calculate the difference type.


Normally the Sort column is the primary key of the table since a PK must be unique and is quickly sorted. When the data compare project runs each table is queried and sorted by the sort column. Then each row is compared and the key columns initially compared. If the key value(s) exist in both tables then at this point the row is the same. If not then the row exists only in either the source table or the target table. This first step determines the number of In Source Only rows and In Target Only rows.


Once the key values are the same then the compare checks each data column for differences and this generates the Different value.


In Source difference type rows will generate an Insert SQL statement, Different rows will generate an Update statement and In Target will generate a Delete statement.


When you focus on a specific row in the data compare results the data details are shown in the data section. In the Data Compare Project Settings you can define the storage options and the storage options determine the data that is available to be displayed in the data results.





Data Results


The data results has two distinct display styles, first and the default is the "above and below" view where the source row is shown as Side A and the first row and the target row as Side B and is the second row. This provides a way of reviewing differences, especially character data, on a character by character basis since the column are aligned. Where there is only one row then the source or target row is shown and for identical data the source row is shown.


Clicking this icon changes the data results display to a "side by side" view where the source and target differences are shown on a single row and for each difference the target value is shown in an added column next to the source value.


See Data Results for more details on the data results panel.


Data Compare Actions


  1. Script generates the sync script ready for deployment to the database server. Since the number of rows can be quite large the script generation process is performed in the background and loaded into the data script tab when complete. The script is always saved to disk first and then loaded into the script manager tab. For this reason the data script is read-only.
  2. Refresh re-runs the data compare.
  3. Print generates a Data Compare summary report showing the same results as the data compare results panel and displays this html page in a separate tab.
  4. Data Export exports the data to file
  5. Opens the project for editing in a separate tab.
  6. Select All/Select None will check or rows remove the check from all rows.
  7. Search for the test in the data compare results list. The buttons to the right perform a next search and previous search respectively.
  8. Filters out of the data compare results list all table/views that only have In Source, Different, In Target and Identical rows. A table that has both difference and identical is not filtered if identical is applied.


Data Compare Script Options


Each table can have its structure altered using specific per table options. The default for the options is set in Data Compare Options

In the below icons the tick indicates the schema element will not change and the cross indicates the schema element will be changed prior to scripting the data and restored once the data has been scripted.


Foreign Key Disable. In some cases you may wish to disable foreign keys prior to adding data. This is especially important where the data does not meet the FK criteria otherwise the script will fail due to constraint failure.

Triggers Disable. You may not want to run a trigger when performing a bulk data change and disabling triggers will drop or disable the trigger prior to scripting and re-create the trigger when data changes have been completed.

Drop Indexes and Constraints. For performance you may wish to disable indexes and constraints and have them recreated once the data has been changed.