Company databases normally contain huge amounts of data. That’s precisely why occasionally database administrators face the issue how to migrate a component of database table to a different format. Intelligent Converters introduced data conversion tools offering smart and versatile solution for such tasks. The product make it possible for you to filter data in the conversion process via SQL SELECT-queries. By using this feature, you can select specific columns, select data subset, merge data from multiple tables or just rename columns inside the destination table. Below you can find samples of using queries for various reasons. All of these queries use MySQL syntax, however it could possibly be easily converted to other dialect of SQL. Imagine that we’ve got MySQL table “Table1” described as below:
Table1(
ID INT NOT NULL AUTO_INCREMENT,
FNam VARCHAR(50),
LNam VARCHAR(50),
Bday DATE,
Notes TEXT
);
Example 1. This query illustrates how to decide on and rename certain columns:
SELECT FNam as FirstName, LNam as LastName FROM Table1
Example 2. The following query extarcts data subset from the table:
SELECT * FROM Table1 WHERE ID > 1000
Example 3. This query skips records containing NULL values:
SELECT * FROM Table1 WHERE Notes IS NOT NULL
Example 4. Merge two tables.
Assume, you have two tables defined as follows:
ProductLines(
productline VARCHAR(50),
desription VARCHAR(1000),
image MEDIUMBLOB
);
Products(
code VARCHAR(15),
name VARCHAR(70),
productline VARCHAR(50),
vendor VARCHAR(50),
description TEXT,
quantity SMALLINT,
price DOUBLE
);
wish to get product code and product name in the “Products” table and outline of products from the “ProductLines” table, it’s possible to be achieved with the following query:
SELECT code, name, description
FROM Products T1
INNER JOIN ProductLines T2 ON T1.productline = T2.productline;
As you can tell, SELECT-query filter is often a powerful feature that permits you to specifically control the info for converting. With this particular feature, it’s so very easy to arrange incremental conversion and migrate exact data you desire. It becomes certainly ideal for daily backup tasks as well as other procedures requiring partial data migration.
There’s one trick to improve performance of filtering queries. The origin database ought to have indexes for almost any filter used in SELECT-query filter. In the example 2 mentioned above it should be an index on “ID” column.
Check out Intelligent Converters official website for additional information regarding products and technologies:
