Using Excel to Compare SQL Database Tables

It is a very common task to compare data content between two SQL database tables. If two tables are within a SQL database, I normally use TSQL EXCEPT and UNION to get difference, detail in my previous blog: Comparing Two Tables By SQL Stored Procedure.

I could add linked server in SQL database so that the same method could be used to compare two tables in two difference SQL databases. However, it would cause too much trouble, such as change SQL settings, or caching millions rows of data from linked server.

The alternative way is very simple. I just run a SQL query to get data in SQL server management studio. Then copy the result to excel, either copy or export. Do the same thing against another SQL server again. By using Excel, it is very easy to compare cell by cell to get any difference.

The following are steps I use in Excel.

Excel Worksheets

The excel file contains four worksheets. The first one is comparison, which will be discussed in detail latter. The second one an third one are data result from two SQL database tables, for example, source_table1 and test_table1. SQL worksheet contains SQL query used to get data.


In the worksheet of comparison, the comparison is done by the following formula. For example, for cell A1 column:

=IF(source_table1!A1=test_table1!A1, source_table1!A1, "Diff (" & source_table1!A1 & " : " & test_table1!A1 & ")")

For the data cell A2:

=IF(source_table1!A2=test_table1!A2, "YES", "Diff (" & source_table1!A2 & " : " & test_table1!A2 & ")")

The formula of A2 comparison applies to all other data cells. Just copy the formula and paste to all the cells you want to compare. Excel is smart enough to adjust cell coordinates to get the comparison results. The following is an example of comparison result:


If you have thousands of raws of data in Excel, you can use Excel's Filter feature to narrow down the differences easily. From menu Data|Filter|AutoFilter to turn on the filter:


On the header filters, you can configure filter to whatever levels you want. Here are two examples:




As you can see, by using Excel it makes much easy to compare two tables and find out the differences.

Import Data Issues

The above method works fine for most of cases. I had a problem to get data from SQL to Excel last week when I was working on one SQL database table. I had spent for 2 hours+ finally I found the problem. Some rows contain double quotes in their raw data. For example, there was " being used in a table as inch unit. This unbalanced quote caused the Excel to import rows from either clipboard or csv file.

Excel treats " as string data type mark. In order to make importing to work, it has to be escaped. I used VIM to resolve the issue. Just simply search for " by typing /" in VIM, it will mark and find those quotes. Then I used VIM's Replace feature to escape all the single quote by the command(replacing " with ""):

:%s/"/""/g

After the normalization of data, I got the expected data from SQL query result to my Excel.

Excel Being too Smart

This issue is beyond the main topic of comparing data between SQL database tables. However, it is worth to leave a note here.

Here is the story of what I do to import or copy data from SQL query result to Excel. For the first data source, I copy the result of SQL query into clipboard. I think that by default, the delimiter used to separate columns is a tab char. I use Paste Special feature from Excel to paste pure text. Then Excel will prompt me to select what delimiter is and preview data in a grid. I like it very much. However, when I repeat the same steps to copy the data result from the second data source, the Excel does not prompt me, nor provide preview. It just copy data to columns. It is too smart to remember the delimiter.

However, I would like to preview data before pasting. I could not find a way to disable this smart feature. Finally I found an alterative way. I pasted the data to VIM first. Then replace the tab to semi-command (make sure it is not used in the raw data):

:%s/\t/;/g

Then copy the whole text to Excel. This change will fool the Excel to prompt the preview as expected.