MySQL : Data Migration Techniques

How to Insert Data to a Table by Selecting from a Different Table

It is often required to insert data in to a table selectively fetching data from a  different table. This is a crucial requirement specially on data migrations, to transfer data into a temporary table or to transfer table under different domain.

Fortunately MySQL facilitates this very conveniently by providing built in functionality,  INSERT ... SELECT .

INSERT ... SELECT Syntax

MySQL INSERT with SELECT Syntax is very straightforward, where the select statement is appended to the Insert Statement with proper column name mappings.

The below example shows a query to insert book data in to a Library Book table from regular book table with proper column mappings which are published after the year 2000,

INSERT INTO library_book (id, name, isbn, publisher, published_year)

  SELECT id, title, publisher, year

  FROM book WHERE book.published_year > 2000 ;

However in the above example you may have noticed that id column is directly mapped. This behaviour is exactly aligned with the requirement and only needed only if you want to make the relation in between other migrated tables. 

For an example, if you have migrated other tables which refers to library book table where it was referring to the old book table, the id should be consistent. If it is not the case, you can ignore it allowing to auto increment or set UUID() function to set an UUID value instead.

However you have to make sure the mappings and the column data types are compatible in between.

References

https://dev.mysql.com/doc/refman/8.0/en/insert-select.html

...