Comparing Database Data

www.altova.com Print this Topic Previous Page Up One Level Next page

Home >  Tutorials > "ZooDB" Database >

Comparing Database Data

It may happen that you have to restore data from a backup database, or that you migrate your database tables to a different database provider. DatabaseSpy provides a database data comparison and merging feature that can assist you in these tasks.

 

Objective

In this section of the tutorial, you will learn how to compare database data and show the comparison results. Specifically, you will learn how to do the following:

 

Set up a data comparison and select tables for comparison
Start a comparison
Show the results in the Comparison Result window
Change the automatic mapping and remove tables from comparison components
Merge database data

 

Commands used in this section

Icon

Command name

Description


Add a New Data Source...

This command is located in the context menu that opens when you right-click the Data Sources folder in the Project window. Click this command to add an additional data source to your project.

ic_save_project

Save Project

This command is located in the toolbar of the Project window; alternatively, you can also choose the menu option File | Save Project As... or press Ctrl+S. Click this command to save an existing project under its project name or to specify a name and path for a new project, respectively.

ic_data-comparison

Data Comparison

This command is located in the Standard toolbar; alternatively, you can also choose the menu option File | New | Data Comparison. Click this command to open a new Data Comparison window and select tables for comparison. Note that at least one active connection to a data source is required to open a new data comparison.

ic_start-db-comparison

Start Comparison

This command is located in the toolbar of the Data Comparison window; alternatively, you can also choose the menu option Data Comparison | Compare tables or choose Compare tables from the context menu that opens when you right-click the title bar of a component. Click this command to run a comparison of all mapped tables in the comparison components.

ic_result-view

Show selected results

This command is located in the context menu that opens when you right-click a table in a component of a Data Comparison window; alternatively, you can also select a table in a component and click the Show result ic_result-view icon in the toolbar of the Data Comparison Result window. Click this command to display the detailed comparison results for the selected table.

ic_show-hide-all-equal

Show/Hide all equal rows

This command is located in the context menu that opens when you right-click into the Data Comparison Result window; alternatively, you can also use the Show/Hide all equal rows toggle button in the toolbar of the Data Comparison window. Deactivate this option to hide all rows that are equal in both of the compared tables.

ic_show-hide-left-rows-only

Show/Hide rows that are only on the left

This command is located as a toggle button in the toolbar of the Data Comparison Result window; alternatively, you can also choose the this option from the context menu that opens when you right-click into the Data Comparison Result window. Deactivate this option to hide rows that exist only in the table that is on the left side of the comparison.

ic_show-hide-nodiff

Show/Hide columns which don't have any differences

This command is located as a toggle button in the toolbar of the Data Comparison Result window. Deactivate this option to hide all columns that do not contain differences.

ic_find-diff

Find

This command is located as a toolbar button in the Data Comparison Result window. Click this command to open the Find dialog box and enter the string that you search for in the comparison result grid.

ic_merge-selected-right2left

Merge data from right to left

This command is located as a toolbar button in the Data Comparison Result window. Click this command to copy data from the table in the right comparison component to the mapped table in the left component.

ic_execute-sql

Execute

This command is located in the toolbar of an SQL Editor window and in the SQL Editor menu. Click this command to execute the SQL in the SQL Editor window.

ic_refresh-datasource

Refresh

This command is available in the Online Browser as a button, or as an option in the context menu that opens when you right-click a database. Use this command to refresh the database and display new or changed database objects in the Online Browser.


SQL and Data | Retrieve data | All rows

This command is located in the context menu that opens when you right-click a table or column in a component of the Data Comparison window. Click this command to have all rows of data displayed in a Result tab.

ic_edit

SQL and Data | Edit Data

This command is located in the context menu that opens when you right-click a table or column in a component of the Data Comparison window. Choose this command to generate an SQL SELECT statement that is automatically executed in the SQL Editor and switches the Result window into the Editing mode. This command works for all supported database kinds for both ADO and ODBC connections.

ic_delete-row

Delete row

This command is available as a button in the Result window toolbar. Click this button to delete the row in which the cursor is currently located. Alternatively, you can also right-click a cell in the row to be deleted and choose Delete row from the context menu. The Delete row command is only available if the Result window is in the Editing mode.

ic_commit

Commit

This command is available as a button in the Result window toolbar. Click this button to commit the changes you have made in the result grid to the database. The Commit button is only visible if the Result window is in the Editing mode.


© 2019 Altova GmbH