Copy table data with common columns

Question

I need to copy data from one table to another. The tables do not have all the same columns, or order; but the data to be copied is always in the same columns; that is data from column foo should be copied to columns foo.

If it was only two tables I could just hardcode the column names like:

INSERT INTO table_target ( column1, column2, column4 ) 
  SELECT column1, column2, column4 FROM table_source;

However there are a couple dozen tables, and some extra transformation needs to be done, so it would be nice if I could just say: Copy any matching columns and ignore the rest.

I've managed to figure out how to get a list of the common columns, but now I'm stuck.

SELECT src.col
  FROM (SELECT COLUMN_NAME as col
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE table_name = 'table_target') as trg
INNER JOIN 
  (SELECT COLUMN_NAME as col
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE table_name = 'table_source') as src ON (src.col=trg.col)
; 

Answer 1

I trick I have used in the past to good effect is to write a query that returns SQL, then just copy-paste it into the db command shell. In this case, this would be your query:

SELECT CONCAT(
    'INSERT INTO table_target (',  
    GROUP_CONCAT(trg.col), -- produces output like "col1, col2, col3"
    ') SELECT ',
    GROUP_CONCAT(trg.col), -- produces output like "col1, col2, col3"
    ' FROM table_source;') as sql_stmt
FROM (
(SELECT COLUMN_NAME as col
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'table_target') as trg
INNER JOIN 
(SELECT COLUMN_NAME as col
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'table_source') as src ON src.col=trg.col) x;

This makes use of mysql's handy GROUP_CONCAT function that aggregates the value into a CSV - perfect for creating a list of column names for generating SQL

I also wrapped your query in an alias so we can select from it.