Home > Documentation > Miscellaneous

Using Queries to Filter Data for Migration

Most of Intelligent Converters database migration tools allow filtering data during the migration process. This feature is implemented through SELECT-queries. It is also possible to modify table structures (change field name, order of fields, add or remove columns, etc) using SELECT-queries.

Benefits

Filtering data during database migration is a powerful technique that allows you to selectively migrate only the data you need, rather than copying the entire dataset. This approach is particularly valuable when migrating large, legacy, or multi-tenant databases, or when you're trying to minimize downtime, reduce transfer cost or improve performance.

Instead of doing a full table copy, you use SQL queries (e.g., SELECT statements with filters, joins, limits) to migrate a subset of the data. This could be:

• Specific rows (e.g., only last 6 months)
• Specific columns
• Data matching certain conditions (e.g., status = 'active')
• Joins across multiple tables
• Transformed data (e.g., mapping legacy codes)

Use Cases for Queries in Database Migration

Examples

The following examples are provided to illustrate how to use queries for particular purposes. Queries are composed according Microsoft Access dialect of SQL. The same is applied to MySQL, PostgreSQL, Oracle and other RDBMS with minor modification of queries. Assume that we have MS Access table "Table1" defined as below:

   Table1(
	ID Integer, 
	FName Text(50),
	LName Text(50), 
	Birthday Date, 
	Notes Memo
   );

1. Convert certain records.

    SELECT * FROM Table1 WHERE ID > 1000

2. Choose and rename columns.

    SELECT FName as FirstName, LName as LastName FROM Table1

3. Skip records containing NULL values.

    SELECT * FROM Table1 WHERE not isnull(Notes)

You can compose SELECT-queries for migration on "Select Tables" wizard page by clicking "Add Query" button.

Common Pitfalls to Avoid

Have questions? Contact us