Migration to MySQL with SQLyog ver 4.1. - Page 3

From the transform-dialogue you choose which tables and columns to import, and you can change the name of each table and column and the data type "proposed" by the ODBC-driver if you like.  Of course, the data type must be valid with the data!  But you can replace, for instance, a "proposal" of the ODBC-driver to create a char(40) data type in the MySQL-database with a varchar(60) if you like. Here I have chosen to replace the "proposal" of the ODBC-driver to create an INT-column with my own idea of what the data type with MySQL should be: a BIGINT.  Also, note that the InnoDB table type is chosen.  You need to do that if you want to import foreign keys.

It is in the "DDL/DML"  that you select or deselect the import of metadata.The other options here are not relevant this time since we are importing to an empty MySQL database.  If there were already data in the database that we were importing to, it would be here that you should decide if you would drop the data and it also would be here that you should decide what to do in case of a conflict with the data already in the MySQL database and the newly imported data.

  Note that this is done for each column independently.  They should not be hard to understand.

To get an idea of the trigger option you could choose to delete one column from a table in the source and you could spot a string type variable in the source and enter a constant string like 'a string' (note it must be surrounded by quotes!) as a trigger value for update.  Open the respective Northwind tables after import and you will find that one column of data has been deleted and one has had its data overwritten with 'a string'.  In the example below, I 'reset' the region-information about customers to some default value "N/A."    Also, note that you can parse the SQL (the UPDATE) that the trigger consists of, by pressing the "Parse"-button.

Note: triggers are only fired against the rows that are imported. So, that lead us to the WHERE clause, which is used for the selection of rows for import.

Here you enter an SQL WHERE statement that will select the rows to be imported.  The keyword WHERE is inserted by the program automatically so do not write it!  It looks like the picture above.  For table "employees" I chose only to import employees who have a 'lastname' ending on 'son' (Hansson, Peterson etc.) Moreover, as I said just above, the WHERE also controls (indirectly) where trigger functionality should be applied.  If no WHERE is specified, all rows will be imported (in SQL no WHERE means EVERYWHERE J), and specified triggers will execute on all rows.  In addition, here you can parse the validity of the SQL-statement before proceeding to next step. Note that with Microsoft Access the use of the Windows' wildcard '*' is legal in an SQL-statement.  Most ODBC-databases would reject this because the corresponding standard SQL wildcard is '%'.

We are now ready to finish the last details: configuration of error handling, email-settings for notification (account-info and the e-mail subject) and finally to decide if the job-description that we just accomplished should be executed immediately from the GUI, saved as an XML jobfile or scheduled as a task with the Windows scheduler. It should all speak for itself.

Above: error handling and general notifications' option.

Configuration of email options: account information (above) and e-mail subject (below).

Press "Next" and the import will start immediately. If you choose to save instead, you can open the saved file from the first dialogue in the wizard at a later point of time, step through the wizard, change things as you like and again have the option to execute, schedule or save.  If you choose to schedule and later want to change the point(s) of time that the job is executed, you can do that directly from "Scheduled Tasks" in the Windows' Control Panel.

Finally, let us have a look at the MySQL database created as a result of what we just did.  In the DATA-pane of SQLyog we see that all the data of the table "products" is there:

In addition, the table was imported with all 'metadata" as we requested and as we can see in SQLyog OBJECTS-pane:

However, no data from table "employees" was imported because there is no employee named "Hansson" or "Peterson" or the like.  You can check yourself that the "employees" table is empty.