Zubair

Update: Its an honor to have ScottGu and Scott Hanselman retweet this post and link from the “Gu” blog and also big thanks to Alvin Ashcraft & Chris Alcock for links to this post and offcourse thanks everyone else for RT-ing it on twitter. If you like this post then please visit back & dont forget to follow me on twitter @zubairdotnet

While playing with Visual Studio 2010 I discovered that the Ultimate (and Premium) edition ship with SQL Data and Schema Compare tools out of the box, so I decided to give a whirl.

I made two databases, SourceDB & TargetDB with only one table Customer. Here’s how the data looked like.

Source	Target

---

The Wizard

Next I selected the Compare Data options on the source table from Server Explorer

This brought up the interface where I chose the target database and hit Next, I decided to leave the other options checked.

Next screen showed the tables and views that I wanted to compare, in my case the only table Customer.

---

The Result

Now the interesting part,

The changed records are off course highlighted.

In addition to the above ones that need to be updated, notice that there is only 1 record in the source table that the target needs to add and 1 identical that will remain unchanged,let’s look at them.

---

The Update

but this doesn’t end here, off course I could update my target database right from VS, for that I had two options, I could generate the script or have VS2010 do it for me.

Since I was playing, I thought let’s see both so I right-clicked and selected,

That brought up the generated Sql script in the editor that I could then use to update my target database,that looked like.

Let’s look a bit deeper,hmm VS disables FK constraints,performs the operation and then restores it back, pretty good.

Since I was a bit lazy so thought I will have VS do it for me, all I had to do was…right-click and select..

VS 2010 then presented a confirm dialog, I knew what I was doing so I hit OK and the changes were pushed to the target table.