Copy tables from the data source- Incremental Import Sessions

Quite often you may face the necessity to repeatedly import data from the same external ODBC source to your MySQL database. Besides, usually you are most likely interested to get only the "fresh" rows, that is the ones which were not imported during the previous import sessions. This is what is called incremental import.

The main problem in organizing incremental import is how to distinguish fresh rows from the imported ones. Import External Data offers you a simple and effective solution for this. With the following two features you can arrange your incremental import sessions:

  • Use the Triggers feature to update your imported rows in their source. Invent the way to mark them as imported ones, e.g. assign a special value to a certain column.
  • Use the SQL Where feature to define a condition that will filter out the rows marked by Triggers.

Look at the following example:

Assume that the table ADDRESS where you need to import from has the string column REMARK which you can modify without negative consequences. Let's use Triggers to have this column filled with the value 'IMPORTED' for each successfully imported row. Then let's use an SQL Where statement to ensure that only "fresh" rows are imported.

  1. After you have checked the box next to the table ADDRESS, press the button Advanced.


  2. Switch to the tab Trigger Options of the appeared dialog.Check the option Update source row.Select the column REMARK in the field Source and type 'IMPORTED' in the field Value.


  3. Look at the automatically generated statement under the grid. Press the button Parse to make sure the statement is valid.


  4. Close the message box and press OK to save the trigger and return to the Import External Data Toolkit Wizard.Press the button SQL Where.


  5. Specify a SQL clause in the appeared dialog. Since you need to filter out the rows which have REMARK set to 'IMPORTED', you clause must be REMARK <> 'IMPORTED'. Please pay attention that 'IMPORTED' must be in quotes and WHERE must be omitted.


  6. Check yourself by pressing the button Parse.

  7. Close the message box and press OK to save the statement and return to the Import External Data Wizard.

  8. Proceed with the Wizard.

Note that later the Import External Data will offer you a choice to run the import immediately, save it as a job or save & schedule the job. If you choose one of the two latter, you will be able to use repeatedly what you have arranged. At that each time SQLyog will import only the rows that do not contain 'IMPORTED' in their REMARK column and then mark imported rows with 'IMPORTED'.

Note: This feature is available in SQLyog Enterprise and Ultimate.