Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Data Comparison with Visual Studio 2010


By:   |   Last Updated: 2010-08-23   |   Comments (3)   |   Related Tips: 1 | 2 | 3 | 4 | More > Comparison Data and Objects

Problem

In my last tip "SQL Schema Comparison with Visual Studio 2010", I showed how to compare schema objects, synchronize them or get incremental deployment scripts. This is good way to synchronize the schema objects between two different environments, but we also often need to compare and synchronize the data that tables (which have the same structure) contain. For example we normally define some master data in a development environment during development and would like the same data to be deployed/inserted/updated to Test/QA/UAT/Production environments after development. So how can we do this, how we can compare data of the tables between different databases and generate data synchronization or incremental data deployment scripts?

Solution

Although there are several different tools available for data comparison (some you need to purchase and some are free) I am going to discuss the Microsoft Visual Studio Database edition for data comparison.

Microsoft Visual Studio Database edition offers several features for database development, for example you can create a database project which is nothing but an offline representation of a database for database development and version control, Database Unit Testing, Code Analysis, Schema Comparison, Data Comparison etc. In this demonstration I am going to show how Data Comparison works on Visual Studio 2010 Ultimate edition although you can do the same with Visual Studio 2005/2008 Database edition too.

Open the Microsoft Visual Studio IDE (Integrated Development Studio) and you will see a "Data" menu in the menu bar depending on the Microsoft Visual Studio edition you have installed (for more details click here). Select Data Compare under Data menu and then New Data Comparison as shown below.

Microsoft Visual Studio IDE (Integrated Development Studio)

You will see a dialog box like this, here you need to specify your source and target database (for which you need to set up a connection to your SQL Server instance). And next you can specify the data comparison options, for example do you want to list all the different records on the result screen or/and records which only exist in source or/and records which only exist in target or/and identical records between source and target. Click on Next to move ahead in the wizard.

 you can specify the data comparison options

On the next screen you can select which tables or views you want to be considered during the data comparison, also you can individually select the columns which you want to be part of the comparison as you can see below. The source and target tables/views must have a primary/unique key which is used as a comparison key during the data comparison. If the table/view has multiple indexes you can select which one will be considered as the comparison key here also.

 select which tables or views you want to be considered during the data comparison

The moment you click on the Finish button in the above screen, it will start doing the data comparison for the selected objects and finally will show the comparison result. You will notice a new tool bar on top, some of the options of this new tool bar are: you can filter out the data comparison result as you can see in the image below, you can synchronize your target tables' data by writing updates to it, you can export your data synchronization/incremental update script to a file or to the editor.

it will start doing the data comparison for the selected objects and finally will show the comparison result

The data comparison result screen will look like the image shown below. On the top pane there are five columns; the first column shows the name of the objects considered in the comparison, second column tells the number of different records between source and target, third column tells the number of records which only exist in source likewise the fourth column gives the number of records which only exist in target and finally the fifth column gives the total number of identical records between source and target.

The next pane has four different tabs, if you notice in the "Different Records" tab the first column "Update" is a check box which you can select/unselect to consider that record in synchronization, next you will see the primary/unique key and then later on all the columns appear twice, the first appearance shows the column value from the source and the second appearance shows the column value from the target. This way you can easily see/compare the changes.

The bottom pane shows the target database data update script depending on the basis of the selection you used above. As you can see, I have two updates and two new records at the source and the scripts appear in this pane. If you are not able to see it or want to refresh it, click on the "Refresh Update Script" icon on the toolbar to display or to refresh the changes.

though SQL Server is case-insensitive, by default, the data comparison is case sensitive when considering name and owner of the table/view.

Note:

  • To compare data between source and target you need to have a primary/unique key or unique constraints on both tables i.e. on source and target tables.
  • Your table might have primary key along with other unique keys, so you can select which key you would like to use as the comparison key in the second page of the wizard.
  • The name, owner and structure must be the same for both source and target tables to appear on second page of the wizard for data comparison.
  • Even though SQL Server is case-insensitive, by default, the data comparison is case sensitive when considering name and owner of the table/view.
Next Steps


Last Updated: 2010-08-23


get scripts

next tip button



About the author




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Wednesday, March 29, 2017 - 7:24:14 AM - Randy Boivin Back To Top

 Im trying to compar dates from a database to todays date if its over 30 day in visual basic 2010

 


Saturday, June 01, 2013 - 2:11:20 AM - farhan mallick Back To Top

how to  view sql query  from  vs on  past database table so  and insrt  the data form  1 cloumn  to  another column 

 


Saturday, March 31, 2012 - 4:29:58 AM - blue world Back To Top

hi ,

i want to automate data comparison in vsts 2010 .

i am using data.newdatacomparison command in command window ,

but its not taking few specific tables .

it is comparing data for all tables. i want to do it for few tables only .

Is there any solution

 


Learn more about SQL Server tools