Data Diff Wizard

The Data Diff wizard compares data between two tables.

Perform the following steps to invoke the Data Diff wizard:

1          From the Tools menu, click Database Diff, and then click Data Diff. The following window displays:

2          If this is the first time you are invoking the wizard, click New.

OR

If you wish to use a previously saved template, click Existing.

3          Click Next.

4          Select the first and second databases by clicking the icons in the center of the screen.

Once the connection is established, the Connection icon changes to Disconnect, depicted as follows:

5          Click Next. The following window displays:

6          If you do not wish to specify any filter, leave the fields blank and every table will be selected. Click Next. The following window displays:

WinSQL Professional displays tables and views on the left, while fields for the selected tables are displayed on the right. If a field has a primary key defined, WinSQL Professional will automatically put a key next to the name.

In certain cases, such as when you select a particular view or your ODBC driver does not support primary keys, WinSQL Professional will not be able to automatically detect a primary key. In this case you must manually create one.

To create a primary key you need to:

·Select the appropriate field

·Click the Toggle Primary Key icon  .

Notes:

·Creating primary keys in this window does not have any affect on the underlying design of the table.

·If you wish to exclude any field from comparison select the field and either double-click it or click the Toggle field selection icon  .

7          Once all options are selected, click Next. The following window displays:

8          Select the appropriate option, and then click Finish.

Comparing Objects Using Data Diff

In order to compare data between two tables every table must meet the following requirements:

·The design of the two tables MUST be similar. You cannot compare tables if their designs do not match.

·There MUST be at least one field as primary key in both tables. If a primary key is not defined, you can specify one using the wizard.

When comparing records, WinSQL Professional selects all records from the first table,, and then issues a SELECT query with appropriate WHERE clause in the second table. If a record is found, it then checks to see if the data in every field is identical in both tables.

Since WinSQL Professional compares the design of every table in both databases while it is fetching, it can take a long time to fetch every object in the database. Therefore, it is recommended that you specify a filter if there are a large number of tables/views in your database.

Running Database Comparisons

During comparison, WinSQL Professional select all records from a table in the first database and compares them with records in the corresponding table in the second database. This comparison only detects rows that match, partially match, or do not exist in the second table. It does not detect rows that are present in the second table but not in first. Therefore, it is important to switch the pivot database once the first comparison is completed.

Select Run comparison with respect to both databases if you want WinSQL Professional to switch the pivot table after the first comparison has completed.

Next, type the location where you want the output generated in Report file name field.

Finally, read the summary and confirm all settings are correct and then click Next.

As with any other Wizard in WinSQL Professional, you can either save the template, or run the wizard without saving it.