Magento 2 Best way to merge specific DB tables and data from one database to another

Question

We have two enviroments Staging and Production, for Magento 2.3.5. At the moment these two enviroments have different database structure, meaning that we can't just dump the entire DB and restore it to the other enviroment. We need to merge Orders, products,customers from one DB to another. I have thought of the following 3 options.

1. Use Magento 2 default import / export function to export orders, products and customers then import to another enviroment
2. dump all relavant mysql tables for orders, products and customers and restore them to another enviroment
3. write an custom module, something like using Magento's resrouce model, to transfer orders, products and customers to another enviroment

which one of the above option would you suggest? are there any other better way to do it?

Answer 1

I would definitely opt for the first option

Use Magento 2 default import / export function to export orders, products and customers then import to another environment

Why do so? Because Magento will manage all entity dependencies and you won't have to be concerned with handling foreign keys, list of tables, mappings and error treating.

You just need to make sure that all relevant attributes and columns exist in the destination database.