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.
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.
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.
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.