Using SQL Views to Migrate Magento 1 Databases to Magento 2

If you are planning to migrate your Magento 1 store to Magento 2, then you will probably use the Magento data migration tool. This tool helps you transfer all your data from Magento 1 to Magento 2, including products, orders, customers, and other information. However, there may be cases where you need to make some modifications to your Magento 1 database before running the migration tool. For example, it could happen, that you have custom tables or fields that need to be included in the migration. Another case we had was, that we used an extension from a third party vendor in Magento 1 and a module from another vendor in Magento 2 for the same functionality. They both stored similar data, but in different tables and formats. As long as there is a proper mapping between tables in Magento 1 and Magento 2 tables, this is easily doable with the migration tool. But as soon as you need to merge information from several Magento 1 tables into one Magento 2 table, SQL views might be helpful. SQL views are virtual tables that are created by executing a SELECT statement on one or more tables. They can be used to present data in a different format than the underlying tables.

Here’s how you can use SQL views to prepare your Magento 1 database for migration to Magento 2:

1. Identify the tables that need to have a different structure

Before you start creating SQL views, you need to identify the tables that do not fit into the Magento 2 database structure. We usually run the migration tool first, as it gives you a pretty detailed report on what tables and fields are missing in Magento 2. Then we go through that list, and either map or ignore the fields or tables which are named in the report. We don’t want to go too much into detail here, as this is pretty well explained in the official Magento documentation. If you already mapped every possible field and table and also ignored all unneeded data, but there are still things to migrate, SQL views might be a good approach.

2. Create SQL views

Once you have identified the problematic tables, you can create SQL views. A good example in our latest migration was a third party module, which added a tree structure to organize CMS pages. We had a proper module for this in Magento 2, but the data structure was different compared to the Magento 1 module. So we needed to create a SQL view to convert the data from the Magento 1 database to the format required by the Magento 2 module. For this, we joined and selected the needed data from several Magento 1 tables, so that the final result fit exactly the structure of the Magento 2 module. We directly named the view like the Magento 2 table, so that the migration tool would pick it up automatically. The amazing thing about SQL views is, that they are always up-to-date, as they are just a virtual representation of the data in the underlying tables. Hence, as soon as data is changed in the original tables, the view will also reflect those changes. You create the view once and don’t have to worry about it anymore. In the end, this looks similar to:

CREATE VIEW `cms_tree` AS SELECT `cms_page`.`page_id`, 
parent_id` AS `parent_tree_id` from `cms_page` 
LEFT JOIN `magento1_cms_tree` 
ON `cms_page`.`page_id` = `magento1_cms_tree`.`page_id`;

3. Run the migration tool

Once you have created the necessary SQL views and updated the migration configuration, you can run the migration tool. The migration tool will use the SQL views to transfer the data to Magento 2.

In conclusion, SQL views can be a powerful tool for preparing your Magento 1 database for migration to Magento 2. They allow you to modify the structure of your database and present it in a format that is compatible with Magento 2. By following the steps outlined above, you can use SQL views to simplify the migration process and ensure a smooth transition to Magento 2.