Learn how to verify data across different database platforms with Toad Data Point

In this video, you'll learn how to verify data across different database platforms using the compare and sync wizard found in Toad for Data Analysts. Quest solution for data access, investigation, and delivery. After you've migrated data from one platform to another using Toad for Data Analysts, the compare and sync wizard lets you verify that data's accuracy, and synchronize data between the two platforms if you find discrepancies. At this point, I'll turn it over to Quest systems consultant, Kuljit Sangha, who will walk us through the compare and sync wizard. Take it away, Kuljit. In order to get started with the data comparing synchronization feature first thing we'll do is go to the Tools menu, go to the sub menu, Compare, and then choose Data Compare and Sync. At this point you will be presented with the wizard. Go ahead and click Next. In order to demonstrate a cross platform, compare and sync, I'm going to go ahead and utilize Excel as my source, and then Oracle as my target. But you can utilize any platform you want. You could use Oracle and Oracle. You could use Oracle and DB2. You can use SQL Server and Oracle. If you're not sure you can go into to the help file and you'll see this table that's provided and help that illustrates which cross database platform comparisons you can make. OK. And you can see here also we have ODBC connectivity. So in case there's the need to connect out to Teradata or Netezza, any of those environments that are not on our main list, you can also use ODBC to connect out to. So once I've made my connections here, I can click Next. And at this point, we're going through the Excel spreadsheet, trying to figure out what type of data you have in there. So what we want to know is, do you want to compare utilizing all the data in the spreadsheet or do you want to only use a subset of the data. In this particular case, I'll go ahead and use the default and just allow the tool to select all the data for me. Click Next. At this point, it will go through and look at your Oracle schema and bring back the list of all the Oracle tables in this schema. Since I'm dealing with Excel or I'm dealing with a non Oracle database, we don't know which database you are trying to compare against. So what I can do is now select the target object. I would like to compare against the customers table. Also what it's going to ask you is which column do you want to do the comparison against. Because my Excel spreadsheet had the proper column names, the columns are already mapped out here for me. Sometimes if they use different names, you also have to map your columns manually. So I'm going to go ahead and compare on customer ID. Hit OK, and then select the Next. Here you have additional options. I'm going to go ahead and choose the default and click Next. And now it goes through and it's identified four differences. I'll hit Finish. Now this is the screen you see once you're done. You can basically kind of redrag and organize your screen into a lot more pleasant view. Up above here, you can see that this is my comparison table. I have 4 differences, no additional, no missing, and 55% identical records. In the middle, you can kind you'll see a summary of the four rows that have differences. And then, by clicking on a row, down below here, I can actually see what the differences are. If you have many, many columns, then you can do cell viewer, and it also just show you that particular cell. So I can click on name, cell viewer, and they'll just show me that. So I'll click back to the row viewer. Here I can go through now my different rows to see all the differences. So my address is misspelled over here. Hoops is misspelled. And then power forward is abbreviated here and the target. So, I say, OK. I've noticed the differences. And then, yes I would like to now go to the next step and synchronize my data. I want to sync up my Oracle Data to make it look like this new Excel data that I just received. So at the bottom right hand corner, I'll click synchronize. Once I click that, it will ask me, do I wish to make my target look like source or my source like target. In this case, I want Oracle to look like my Excel spreadsheet. Hit Next. And then, it'll ask me, do I want to open the script in the editor. And I'll say yes, and hit Next and finish. At this point, I can now see the script that was created for me. And what I'm going to do now is actually show you another feature. I'm going to go ahead and execute this script in my Toad editor, and all my updates were completed successfully. Now I'm going to go back to my data compare wizard. Go to window and then flip back to my data compare window. Once I'm back in this window, the really cool feature is, in order to make sure that your updates were done successfully, I can now come back and hit refresh. And what it's going to do is it's going to run through that comparison again by refreshing my Oracle Data now. Once it's completed, you can see now that there are zero differences. So the four differences I had were fixed by running the update script in my Toad editor. So that's an example of how to utilize the data compare and sync wizard in the Toad for Data Analyst product against multi-platforms. For more information about the Toad family of products, visit ToadWorld.com. At ToadWorld, you can find more tips and tricks videos, get direct access to industry experts, and network with other developers just like you. ToadWorld.com, your one stop resource for education, expertise, and collaboration.