ID #1063

Can I perform "incremental import" with the SQLyog Import External Data Tool?

 

Yes! The SQLyog Import External Data Tool was developed with that feature in mind from the very beginning. To do this you must understand how to use "filtering" of the data to import, and how to use the "trigger" functionality of the SQLyog Import External Data Tool. So let's start having a look at that:

FILTERING:

The filtering options allows for "horizontal" as well as "vertical filtering". Together, they constitute "grid filtering.". You can choose any subset of rows and columns to import. The "horizontal filtering" is simply an ability to select/deselect tables and columns for the import. That is done from a checkbox in the GUI-wizard. The "vertical filtering" is an ability to select/deselect rows for the import. The selection of rows to import is done by specifying a WHERE-clause to execute against each row on the source.

TRIGGERS:

The "trigger" functionality is the ability to perform changes to the ODBC-database from where data is imported. The changes take place with each row of the source after data has been saved in the MySQL-database. The trigger functionality simply sends an SQL-statement to the ODBC source database. The statement will be executed on the row that has just been imported to MySQL. The two SQL types of statements available are DELETE and UPDATE.

The DELETE trigger option simply deletes that row of data from the source after it has been saved to the MySQL database. The UPDATE trigger option lets you perform UPDATES to the source database on the row in operation. This option is available and configurable for each individual column. You simple enter the "last half part" of a normal SQL UPDATE-statement (what comes after UPDATE TABLENAME SET...).

Note that what you enter as an UPDATE-trigger sent to the source database must follow the syntactic rules of the SQL-"dialect" of the source database. Thus, not only constants but also functions and expressions are possible - actually, any expression that is legal with the syntax of the SQL-dialect of the ODBC-source and the ODBC-driver used for the import will work. With MS-Access you can enter "date()" and the current date will be written to the source – provided that the date()-function is valid with the column-definition. In addition, you might increment a "mycolumn"-column by entering "mycolumn+1" as the UPDATE-trigger to execute against the "mycolumn"-column.

Note that this is a trigger-functionality of the SJA/SQLyog itself. It has nothing to do with any (native) trigger functionality that might exist with the database imported from – or with the trigger functionality of MySQL version 5. It is something of its own.

INCREMENTAL IMPORT:

You probably have seen the light already. When importing a row (or a subset of a row) you TRIGGER that row somehow. Write a 'codestring' of some kind or a time value to a certain column of that row. To do incremental import you simple need to use the WHERE filtering to test for whether (or when!) a certain column/row combination has previously been TRIGGER'ed.

Due to its unique facilities in this respect the SQLyog Import External Data Tool is equally well suited for both these situations:

  • One-time migration to MySQL.
  • Permanent coexistence of MySQL and other databases within an organization.
  • ... and all possible intermediates to be thought of.

 

Tags: -

Related entries:

You can comment this FAQ