SQL Data Comparison - User Manual

You visit this page because you know how to write a SQL query. Learn how to use your expertise to do your work better and save time!

The Idea

The Selectcompare idea is very simple: write an SQL query to extract data to compare each data source.

With Selectcompare, you create comparison projects that define data connections to each of the data sources and you write SQL queries for these data connections.

You can create data connections separately from comparison projects, and reuse data connections in multiple projects.

This quick manual will show you how to compare data using Selectcompare.

Step by step

When you launch the program, you are presented with the page containing all defined comparison projects. Initially, the list is empty.

On the bottom part of the screen, you see two icons. The  icon is used to create a new comparison project, while the  icon is used to access your defined connections.

Create data connection

SelectCompare can use data from any ODBC data source that is installed on your machine. When you click the  icon, the program will display a page with a list of defined connections. Initially, this list will also be empty.

Here, to create a new connection, you have to click the  icon.

You will be presented with a form that allows you to enter the details of the connection:

You can assign a color to connections to visually separate them from each other, for example, baseline from the test environment, or Oracle and SQL Server.

The Connection string field can be populated either by pasting a connection string from a different source or  https://www.connectionstrings.com/, or by building it using the system dialog.

The list of data sources depends on what data providers are installed on your machine. Depending on the selected data source, the further steps of creating a connection will vary. I will cover some of the scenarios in future posts.

The resulting connection string is returned to the Connection string edit box when the connection is created. You can make changes to it, or click the Test Connection button to verify that the connection string is valid.

The screenshot below shows a sample connection to an Access database.

Selectcompare allows you to connect to any data source supporting ODBC or OLEDB data provider. This example shows an MS Access connection, but SQL Server, Oracle, MySQL, and PostgreSQL are natural candidates - read on!

When the connection is created, you have to save it to use it in data comparisons.

Create a data comparison project

The main page of Selectcompare shows the list of all projects that you have defined in the program.

A comparison project defines source and target connections, and the corresponding queries associated with those connections.

To create a new data comparison project, you click the  icon.

Similarly to the creation of a new data connection, you can select a color with which the project will be associated. This helps to visually group data comparison projects by their purpose, environment, or other criteria that you may find helpful.

Next, you can define the selected statement that will be used on the source side of the comparison.

When you click the Edit button, the "Edit query" screen is shown.

On this screen, you select the data connection created in the previous step and enter the select statement that will be executed against this connection: 

The syntax of the select statement that you enter must be compatible with the data connection that you use. When you define the query, you can preview the data or export it to Excel using the icons at the bottom of the screen.

When the source query is defined, just click the  icon to go back to the comparison project screen.

The next step is to define the target query. For this example, both source and target connections point to the same test Access database, but they don't have to. 

You can compare data between different data sources, for example, an Excel spreadsheet and a database.

As you can see, for this example the target query selects from a different table than the source query.

When the target query is defined, you click the  icon to go back to the data comparison project screen.

Data comparison results

Now, when the source and target sides of the data comparison are defined, you must select which columns should be used for the join key, and optionally, which columns should be ignored during the data comparison.

Note: Only columns that have the same names on the source and target select statements can be used in comparisons. If there are columns on either side that don't exist on the other, they cannot be selected for comparison. 

In this example, since the queries group orders by SalesOrderId, this column can be used as the key to join the data sets from source and target:

After selecting the key and columns to use in the comparison, just click the  icon and, after saving the changes, the data comparison is executed.

When the comparison is finished, you will see the results screen.

The screen by default displays only rows that have differences or that exist only on one of the sides. You can switch on and off portions of the data shown by clicking the filter icons above the results grid.

Columns coming from the source and target sides of the comparison are prefixed with S_ and  T_ respectively. This allows you to navigate through the data easily.

The results grid allows for the sorting of the data by each column. Sometimes the simple view is not sufficient and additional analysis is required. In such cases, you can export the comparison results to Excel by clicking the icon at the bottom of the screen. Excel provides advanced features, charting, pivot tables, etc that you can use to analyze data.

What next?

Check other articles describing various aspects of the usage of Selectcompare.

Some of them are listed below:

• Write SELECT statements for Excel
• Create an Excel baseline for your data
• How to compare data with a baseline
• Configuration of an ODBC data source for Oracle
• How to install Cherry City OLEDB provider for MySQL
• Activation of Cherry City OLEDB provider for MySQL