Data Compare in Toad for SQL Server

Hello, everyone, and welcome to another Toad video demonstration. My name is Gary Jerep, and I'm a solutions consultant for Quest Software, specializing in our database management solutions. As many of you know by now, Toad solutions provide database professionals everywhere many important capabilities like administration and management of your database objects, code debugging, performance tuning, compares and syncs, and a whole lot more. In this video, I'll be concentrating on giving you a high-level overview of Toad for SQL Servers Data Compare. And along with that Data Compare comes a nice synchronization feature. So as you can see, I've launched Toad for SQL Server, and I'm already logged in to my database. As you see from the object explorer, I do have a Gold Standard database and also a dev version of that with similar tables in that database. What I'd like to do here is to compare table by table, view by view the elements in my Gold Standard database to the objects in my dev one. So I'm going to highlight those two databases, right-click, and launch the Data Compare Wizard within Toad for SQL Server. Note, you can make source look like target or use target as a source. I'm going to keep my dev Gold Standard database as the source here and move through the wizard. The first thing that Toad does is attempt to pair, for comparison reasons, table-to-table. So we take a look at the table names, of course. Now it doesn't mean that you have to accept the pairing that Toad for SQL Server gives you. For example, I may want to change my comparison on the target side, so in fact, highlighting my customer table on the target. I do have a derived customer table that I created. In fact, that's the table that I'd like to compare. So I just changed the pairwise comparison there and changed the target to be this derived table. Notice, also, that for any of the tables, I can perform column mapping. So if the column names are not the same, or there are additional columns, or some columns need to be mapped to other columns, or the columns are not in the right order, I can choose a different column for the target comparison. In these cases, in all my cases, the columns are the same. They're named the same. Note, by the way, however-- and we do call this out with a different visual icon-- the source column of First Name has a different data type-- actually, same data type, but different length. Note that. And that's why that icon is different, just to identify that, make you aware of that. If you're OK with getting first name truncated on target, that's fine. Leave it as it is. Otherwise, you might have to change the data type there for the first name. I'm OK with that. Notice that we chose the proper comparison key. Notice, also, two other things-- I don't have to compare all the columns, so pick and choose. And if you wanted to be very specific about which rows, you can specify a WHERE clause condition here either at the source and/or at the target for any of your tables to limit the comparison. In this case, I'll just simply say, OK, I'm happy with that pairing up of the source and the target tables-- actually, not too happy. I don't really care about views here. So a quick way to do this-- and especially true if there's hundreds of objects that have been paired between the databases. In my case, I don't care about the views. So anything that has the name "view" in it, I'm going to unmap so I don't have to consider those. And you'll see, when I look at the total list here, I'm not going to consider any view targets for the comparison. I'm OK with all this. There's no red Xs. That's great. I'm going to move through the wizard. Yeah, Toad is really known for this-- lots of flexibility in terms of settings and options. You want to ignore blobs. You want to ignore text, case in the text or white spaces. Pick and choose the things that are important to you. Otherwise, we can move through the comparison wizard. Just read now through all the data in my small databases here. You can see that most of the tables are the same and have equal and matching records. Some do not. So if I go ahead and say Finish, we get a nice doc tab here representing the visual results of my data compare pairwise table to table. Now, note, for example, that some of these objects have records that are only in the source and not in the target. Those will show up in green. If I had records for a table in target only and not in source, those will show up in red. But for the rest of these, I have lots of differences here. So this top panel will give you a list of the pairs of tables we're comparing. The middle panel here, once I choose a pair of tables that we've compared, will show you any differences. Now, by default, we're going to show you all the records. And again, light green for records that are in the source, not in the target. Records that are in the target, not in the source. However, you can choose. So in my case, I'm just interested in the differences. I'll tap that icon. And now I'm only seeing the records that are different in both source and target. That's the middle panel. By the way, I am using a side-by-side or a grouped column effect where I can see per column source and target. If you don't like that, you can see the entire source record first and then the target record that it compares to. I kind of like the column groupings, which is really nice.