Copy table data with shared columns

Question

I need to copy data from one table to another. Tables do not have the same columns or order; but the data to be backed up is always in the same columns; that is, data from a column foomust be copied to columns foo.

If it were only two tables, I could just hard-code the column names, for example:

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

However, there are several dozen tables, and some additional transformation needs to be performed, so it would be nice if I could just say: copy any relevant columns and ignore the rest.

I managed to figure out how to get a list of shared 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

, , - , SQL, db. :

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;

mysql GROUP_CONCAT, CSV - SQL

, .