Toad’s “data grids” have always offered numerous, useful “export data” or “save as” capabilities. Two of the most popular are to export the data grid as comma delimited text (for import into spreadsheets) and Microsoft Excel files. In fact Toad/Microsoft Excel interaction (both export and import) is so popular that I’ve written several detailed blogs on Toad data grid “to” and “from” Excel capabilities:
Now Toad 12.1, releasing Sept 19th 2013, adds a flexible and powerful new “export data” or “save as” capability – via ODBC data sources (i.e. Microsoft Open Database Connect). Thus now you’ll be able to save your data grid results directly to any other database – such as MySQL, SQL Server, DB2, PostgreSQL, Sybase, and so on. Plus Toad’s Import Table Data wizard will also be able to load Oracle tables directly from those same databases. In short Toad 12.1 offers some very basic ETL (Extract, Transform and Load) capabilities. Furthermore if you utilize Toad’s “App Designer” and ability to run via command line, then you actually have a very basic ETL tool! See these blogs below for examples of how to expand upon the basic ODBC demos in this blog to create fully automated ETL jobs that you can schedule using Toad.
NOTE – Toad 12.1 ODBC capabilities are limited to just two areas: data grid “export data” or “save as” and the Import Table Data wizard. Toad’s “Login” window does not support generic ODBC connections. Hence Toad 12.1 is not a general purpose, multi-database tool – for such needs you’ll still want a tool like Toad Data Point (formerly Toad for Data Analysts).
Pre-Requisites to Using ODBC
ODBC is part of Microsoft Windows. But it is not complete – only the fundamental portions are installed and available by default. ODBC has two layers – the upper portion or general API, and the lower portion or database platform specific drivers. The upper portion is there by default. The lower portion you must install and configure based upon your needs – lest you cannot do much of anything.
In my case I want to demo Toad working with MySQL and PostgreSQL. Note that those two databases are running on remote database servers and not on my PC. So the first thing I must do is to visit each databases’ web site, download their ODBC drivers and install them. I can confirm this via the Windows “Add/Remove Programs” screen as shown in Figure1. Remember, if you do not install the ODBC drivers then you cannot get ODBC to talk to those databases.
Figure 1: ODBC Drivers Installed
Once I have my ODBC drivers loaded, next I need to configure the ODBC data sources to point to my actual databases. Thus I open Windows’ ODBC Administrator utility as shown in Figure 2, choose whether the new data source name (DSN) will be just for me (User DSN) or anyone (System DSN), press the “Add” button, and then fill in the relevant connection details as shown in Figure 3 for MySQL and Figure 4 for PostgreSQL. Of course your connection details will be different. Think of this step as similar to defining the TNSNAMES.ORA alias. I highly recommend using the “Test” button to verify that you got everything right (i.e. connection works) and that the DSN is now truly ready for general use – including being used by Toad.
Figure 2: Windows ODBC Administrator Utility
Figure 3: MySQL Connection Details
Figure 4: PostgreSQL Connection Details
Export Data to ODBC Data Source
I’m not going to cover in detail the export data – rather I’ll refer you to read the blog Copying Database Data to Microsoft Excel via Toad. But look at Figure 5 below. I’m in the Toad Editor, have a simple SQL statement, executed that SQL to populate the data grid, pressed the export dataset button, and chose ODBC as my export format – which causes the options tab to redraw with ODBC specific data for me to fill-in. So I open the “ODBC Connection” drop-down to display all my previously configured ODBC DNS’s, and select in this case the one for MySQL. Then I provide the MySQL connection info such as user name and password. Finally I open the “Target Table Name” drop-down to display all my existing MySQL tables to choose where Toad will send the exported data. That’s it – now just press OK and viola’ it’s done. I’ve written my CUSTOMER table from Oracle into MySQL.
Note that the target table must exist, its layout must approximate the Oracle format (i.e. same column names and equivalent data types), and it must not contain existing rows that would prevent the new rows from being inserted (i.e. no key violations). Finally note that Toad does not offer the capability to auto-magically create the target table if none exists. That’s because it’s far too complex to know every data type mapping between Oracle and whatever database you export to.
Figure 5: Export Data to ODBC DSN
Import Data from ODBC Data Source
I’m not going to cover in detail the Import Table Data wizard – rather I’ll refer you to read the blog Create and Load Oracle Tables from Excel Spreadsheet using Toad. First look at Figure 6 below. I’ve chosen to import into a newly created BERT.CUSTOMER table – you can tell that it’s new or at least empty because pressing the “Show Data” button displays only the column headers and no actual row data. Now look at Figure 7 – the Import Table Data wizard where I specify to use ODBC. Note that it’s pretty much identical to export data screen in Figure 5. Again first I chose ODBC as my Import from – which causes the lower portion of the screen to redraw with ODBC specific data for me to fill-in. So then I open the “ODBC Connection” drop-down to display all my previously configured ODBC DNS’s, and select in this case the one for MySQL. Then I provide the MySQL connection info such as user name and password. Finally I open the “Source Table Name” drop-down to display all my existing MySQL tables to choose where Toad will load the data from. Then I pretty much just pressed “Next” until I reached the end of the import wizard, and then pressed the “Execute” button – resulting in 62 rows being loaded from MySQL into Oracle as shown in Figure 8. In a future blog I’ll go more in depth on how to perform the “T” or translate in ETL and then how to schedule such resulting ETL jobs.
Figure 6: Import Table Data - Page 1
Figure 7: Import Table Data - Page 2 – ODBC
Figure 8: Import Table Data - Last Page - Done