Loading the Data into the Database
Now that you have transformed the data into your desired format, all that remains is for you to insert the data into an Informix database. Because I'm using WinSQL in this article, I'll show you how to do it with that product.
There are two versions of WinSQL available: the regular version, which is a free download, and the Professional version, which costs about $100. Everything you've done up to now has been done with the standard version. In order to create templates for moving the data from database to database, you need to switch to the Professional version.
If you don't care to pay for the tool, you can still do all of the data massaging you've done up to now—only the process for getting the data into the new database will be much more kludgy. You'll have to print the massaged data to a flat file and then use an Informix tool such as dbaccess or dbload to load the data into the database.
 |
|
Figure 7. Data target is table "newtable" in another database
|
With the Professional version of WinSQL, you can create a template that allows you to do the insertion into another database automatically.
First, click on the far right-hand button in WinSQL to bring up the export screens. Then choose New to create a new export template. Choose to create a new table in a new database (see Figure 7).
 |
|
| Figure 8. Choosing the target database |
After clicking Next, you have the chance to choose the target database in which to create the table called "newtable" (see Figure 8). This is not obvious from the screens, but here it is asking for the target database, not the source.
 |
|
| Figure 9. Verifying the new table structure |
Once you click OK, you will be shown a screen that verifies the structure of the table to be created (see Figure 9).
If you are not happy with any of the defaults in the column names and data types, change it here before the new table is created.
 |
|
| Figure 10. Mapping the data |
Once you have verified the data structure to be created, you will get the screen shown in Figure 10, which allows you to map the incoming data into the new table. Because you're creating a new table, there should be a 1:1 correspondence between the source and target tables. If you were importing into an existing table, you'd use this screen to map the old data to the new data.
 |
|
Figure 11. Running the export
|
When the data is mapped, you need to choose whether to run the export, run it and save it, or just save it (see Figure 11). If you plan on doing this type of exporting on a regular basis, be sure to save the template.
When you click Next, you will be given a final chance to verify what is happening. Click Finish to transfer the data into your other database. Note that this does not have to be an Informix database. It can be any database for which you have an ODBC driver set up.
 | Joe Lumbley is the author of The Informix DBA Survival Guide, the best-selling Informix book in the world. Joe operates an Informix consultancy, JP Lumbley & Associates, LLC, which specializes in database administration, performance tuning, and CRM systems. Joe can be reached at joe@lumbley.com. |
