I have already written two articles regarding Data Dude (aka Visual Studio Team Edition for Database Professionals); one published as Schema Comparison with Data Dude and other published as Test Data Generation with Data Dude. This is my third article regarding Data Dude, and in this article I will illustrate the way of doing “Data Comparison” with Data Dude. Since both my previous articles provided an introduction to Data Dude, I decided not to add the same here. Let’s see what “Data Compare” is. Data Compare allows us to make a comparison of data between two databases and update the target. Just like the “Schema Compare” operation, this requires a “source” and “target” too. On comparison, it allows us to see the differences and update the “target” as we want. We will perfrom a simple comparison and see how it can be used and how it is useful for us. The first step is, creating a database project. And we need two databases with the same schema for comparing data. Since I have covered the initial steps for creating the project model with my previous articles, I am not going to show them here again. Simply follow the steps below;
- Open “SQL Server Business Intelligence Development Studio” and create a “SQL Server 2005” database project. Since we do not want to load a schema in to the project, do not use the “SQL Server 2005 wizard”.
- Let’s use ther “AdventureWorks” database as the source for the comparison. As we need to have a target database with the same schema, let’s duplicate the “AdventureWorks” database. So open SQL Server Management Studio.
- Backup the “AdventureWorks” database and restore the database as “AdventureWorksTemp”.
- Now go back to the project in BIDS. Though there are no differences between data in the two databases, we will see a way of performing the data comparison between the two databases. Click on “Data -> Data Compare -> New Data Comparison” menu item.
- It opens the “New Data Comparison” window as below.
We need to set the source as the “AdventureWorks” database. If the drop-down contains the connection for the database, select it, or else make a new connection by clicking on the “New Connection” button and select it.
- Select the target as “AdventureWorksTemp”. Again, if it is not in the drop-down, create and select.
- There are four Data Compare options in the screen;
- Different Records: This results the records available in both databases that have differences. Records will be updated in the target database.
- Only in Source: This results the records only in the source database. Records will be inserted to the target.
- Only in Target: This results the records only in the target database. Records will be deleted from the target.
- Identical Records: This results similar records in source and target. No action will be performed against target for these records.
- Select all checkboxes. Click on “Next” to continue.
- It opens a window like below;
This allows us to select the objects we want to do the comparison of data on. If you want to exclude any object, expand “Tables” and de-select items you want to exclude. Click on the “Finish” button to start the operation.