Thursday, July 08, 2010
Fun with VS2010 Ultimate: Comparing SQL Data
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,
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.
I can see the options to "Compare Schema" and "Compare Data", but they are disabled. Guess the feature isn't available for Professional? :(
It's a very welcome addition.
Do the SQL Data and Schema Compare tools require Team Foundation Server and a Team Foundation Server CAL?
"Do the SQL Data and Schema Compare tools require Team Foundation Server and a Team Foundation Server CAL?"
No,there is no such requirement.