Comparing table data

AQT provides a basic mechanism for comparing the results of two queries. This can be used to:

• compare the contents of two tables. This works even if the tables are in different databases
• compare the contents of one table at two different points of time. This is useful for
  • time-varying data
  • checking the result of a large update
  • comparing the results of test scripts.

There are two ways you can use this feature:

Use the Compare to Another Results Window

This is the best method to compare two tables:

• display the first table or query
• without closing this window, display the second table or query.
• if you find that you can only display one table at once, click on the top-left icon to “lock” the Data Display, or use Options > Window Behaviour > Always open a new Data window.
• while displaying one of the tables, click on Compare > To Another Results Window. You will be shown a list of all the other Result Windows. Select the appropriate window.
• The data in the two grids will be compared, and you will be shown the Compare Results window with the results of the compare. This window is described later in this topic.
• If you want to exclude some columns from the compare, you can do so with options in the Compare Results window.

Use the Compare to Saved Results

This is the best method of comparing a table at two different points in time.

• display the table or query
• click on File > Save Results to save the data to disk. This data can then be recalled or compared-to at any time in the future.
• (later) redisplay the table or query.
• click on Compare > To Saved Results. Select the file where the results of the earlier query were saved.
• The data will be compared, and you will be shown the Compare Results window with the results of the compare.

Warnings

For the compares to work correctly you should always display the data in the same order. It is recommended that you have an Order By primary-key-columns in your Data Displays.

The compare is a “simple” compare; it is not smart enough to cope with rows being inserted or deleted. If this has happened, it will flag differences in all subsequent rows.

Make sure you are displaying all the rows in your tables by setting Options > Display Limits. Also make sure that Max Column Size is large enough for your widest column.

This feature cannot compare BLOBs, large binary fields, or columns larger than 10,000 bytes.

Save Results will save a maximum of 10,000 bytes per row. If you have some very large columns in your display, they may not be saved / compared correctly.

Compare Results window

This window shows the results of your data compare.

• the boxes at the top of the Compare Results window give you details of the queries you are comparing.
• Compare Cols allows you to select the columns you are comparing. By default, AQT will compare all columns in your data. You can redo the compare excluding particular columns from the compare. Click on the >>> to expand the column-list then select or de-select columns as appropriate. Click on Do Compare to rerun the Compare.
• the grid will have one line per difference found in the data. As you select an entry (either by clicking on it, or by using the up/down keys) AQT will tell you at the bottom of the screen what the difference is between the values.
• as you select entries, the related values in the table-display windows are highlighted. This makes it easy to see, in the table-display windows, the values that are different.
• you can double-click a cell value to see the full value. This is useful if you have very large values.