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.

Source Target
sourcetable targettable


The Wizard

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

selectcomparedata

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

datacomparewindow

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

comparehitfinish



The Result

Now the interesting part,

datacompareresults

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.

onlyinsource

onlyintarget



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,

showscript

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

targetscript


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

writeupdates

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.

Labels: ,


Comments:
Very cool feature. Microsoft should do a better job of making people aware of this. Since it is similar to competing products such as the Red Gate Sql Compare & Sql Data Compare this could save some money for some folks.
 
Thats right,exactly why I wrote it up so more folks out there can know that it now exists out of the box.
 
Awesome post. You have a new follower :-)
 
I have VS2010 Professional.

I can see the options to "Compare Schema" and "Compare Data", but they are disabled. Guess the feature isn't available for Professional? :(
 
I wish MS had added this feature many years ago, and to the SQL tools rather than VS.

It's a very welcome addition.
 
The Visual Studio 2010 version comparison page says Database Change Management requires 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?
 
Wondering do people really need this kind of tools in real life?
 
Thanks Doug :-)
 
James,yes thats true this feature is only available in Ultimate and Premium versions of VS2010 according to MSDN,cant find the link anymore where I read that.
 
@Gordon

"Do the SQL Data and Schema Compare tools require Team Foundation Server and a Team Foundation Server CAL?"

No,there is no such requirement.
 
@cpdas Yep! RedGate cut their teeth selling these tools. I know a lot of people who bought, and regularly use them, myself included.
 
Its cool but do we have a way to program this from code and find the difference just like red gate SDK.They have VSTSDB.exe command line but that doesnt give me the right result for comparison. i took the same schema copied and gave it to compare and it shows be the some script to update through VSTSDB.exe but from the UI it says they are identical. Do you guys know if there is a way to reference any dll's to compare Schema in VS2010?
 
This is an interesting article! But I like the way data compared in dbForge more, also I like the command line support available there (the only thing is that I don't know for sure if command line support is available in "VS2010 Ultimate: Comparing SQL Data" or not?).It is possible to get a free license to dbForge Data Compare.
 
Post a Comment



<< Home

This page is powered by Blogger. Isn't yours?