SQL Data Comparison with Visual Studio 2010
SQL Data Comparison with Visual Studio 2010
By: Arshad Ali -- 8/23/2010
Rating:<!-- Rating BEGIN -->
Problem Solution 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. 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. 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. 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. 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. Note:
|