How to copy millions of rows from one table to another?

Question

MySQL

There is a task on the sale, to copy millions of rows from one table to another, checking whether there is such a record when inserting. Performance is very important so that the query does not run for several hours. The structure of the tables, the set of fields are different.

The first thing that comes to mind is:
1) Write a doctrine migration, most likely it will be very slow
2) Write a SQL query that can be run in the console, which is fraught with long execution, especially if there are subqueries
3) Limit the number of records processed at a time, add a limit or batch processing
4) Use some built-in copying, backup tools.

Answer 1

INSERT INTO ... 
SELECT ... 
WHERE ...
LIMIT ...

https://www.w3schools.com/sql/sql_insert_into_sele...
Depending on how different the tables are and which fields are NOT NULL, you may need to fill in some data. On a small sample, you must first debug the SELECT query with the necessary transformations and indexes. Once the SELECT works well for different cases, run a partial migration and see if it works. Then, emptying the target table with TRUNCATE TABLE, run a full migration through Doctrine, provided that it can generate such a query correctly, of course.
Millions will work fine.
Added
To control duplicates use
https://www.mysqltutorial.org/mysql-insert-or-upda...
- update or ignore