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 database migration tools offering smart and versatile solution for such tasks. The product makes it possible for you to filter data in the migration 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. Let us consider two tables that are 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
);
If you 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 data during database migration. With this particular feature, it’s so very easy to arrange incremental migration by filtering the data you desire. It becomes certainly ideal for daily backup tasks as well as other procedures requiring partial database 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 in the database migration field: https://www.convert-in.com
