SQL Data Comparison - User Manual


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

The Idea

The SelectCompare idea is very simple: write an SQL query to extract data to compare on 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 is a quick manual that 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  icon is used to access your defined connections.

Create data connection

SelectCompare can use data from any OLEDB, ODBC or .NET 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, in order to create a new connection, you have to click the Add new data connection icon.

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

Create new data connection for data comparison

You can assign a color to connections to visually separate them from each other, for example baseline from 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 from https://www.connectionstrings.com/, or by building it using the system dialog.

Choose data source driver

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.

When the connection is created, the resulting connection string is returned to the Connection string edit box. 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 MS Access connection, but SQL Server, Oracle, MySQL and PostgreSQL are natural candidates - read on!

 

Sample Access data connection

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

Create data comparison project

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

SelectCompare data comparison projects

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 Add new data comparison project icon.

Create new data comparison project

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 select statement that will be used on the source side of the comparison.

Define source select for data comparison

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

Edit select statement for data comparison

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: 

Source select statement for data comparison

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 Go back to comparison edit screen 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.

Target select statement for data comparison

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 Go back to data comparison project icon to go back to the data comparison project screen.

Create new data comparison project

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 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 the 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:

Select join key for data comparison

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

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

Data comparison results

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 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 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 analyse data.

What next?

You are now ready to chose the edition of SelectCompare that will suit your needs. Over one hundred data professionals already have done so!

Featured Products



Leave a comment


Please note, comments must be approved before they are published