Specifying a Query

The option to write a Query can be choosen only if the tables need to be re-structured or if only a few rows need to be imported that satisfy a certain condition. The example below illustrates how to use the option to 'specify a query' in order to import data from 'sakila' (used as the source database) to another database where we will re-organize information from different tables in 'sakila' to a target table with two columns consisting of :
1. An integer `customer` column where every customer is identified with the same id as the customer_id in sakila.customers table.
2. A string `label` column (char, varchar or TEXT) where the information is formatted like a label in printable format consisting of full name, address, state and country.

To obtain information from various tables in 'sakila' like customer, address, city and country the following query can be made use of.

SELECT `customer`.`customer_id`,
`customer`.`first_name`,
`customer`.`last_name`,
`address`.`address`,
`address`.`address2`,
`city`.`city`
FROM
`sakila`.`customer`
INNER JOIN `sakila`.`address`
ON (`customer`.`address_id` = `address`.`address_id`)
INNER JOIN `sakila`.`city`
ON (`address`.`city_id` = `city`.`city_id`)
INNER JOIN `sakila`.`country`
ON (`city`.`country_id` = `country`.`country_id`);

The output of the above query when executed in the source database "sakila" looks as follows:

Now we will use a variant of this query to import data from 'sakila' to a new table as described above. The table is assumed to be created in advance. With the necessary formatting added the above query will be rewritten to:

SELECT `customer`.`customer_id` AS customer,
CONCAT( `customer`.`first_name`, ' ',
`customer`.`last_name`,
'\r\n',
`address`.`address`,
'\r\n',
`address`.`address2`,
'\r\n',
`city`.`city`,
'\r\n\r\n',
`country`.`country`
) AS label
FROM
`sakila`.`customer`
INNER JOIN `sakila`.`address`
ON (`customer`.`address_id` = `address`.`address_id`)
INNER JOIN `sakila`.`city`
ON (`address`.`city_id` = `city`.`city_id`)
INNER JOIN `sakila`.`country`
ON (`city`.`country_id` = `country`.`country_id`);

The aliases 'customer' and 'label' also define the column names of the target table where the data are imported. So the table that was created in advance should use those column names. The screen shot below shows the page in the wizard where you can enter the query.

After the successful completion of the import process the table "result" looks as follows:

You can use any valid SQL construction (a WHERE-clause, a GROUP BY with aggregates etc) in the the query you specify. However the following two points need to be taken care of:

1) The SQL entered when selecting to 'specify a query' is executed on source database. So the 'SQL-dialect' used in the query should follow the rules of the source server (SQL Server, Oracle etc. syntax may differ from the MySQL syntax in some respects).
2) When using the option to 'specify a query' you can only import to a single table per job.

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