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)
;

can you use dynamic sql? are you using SQL inside another programming language? — golimar, Jan 13 '12 at 17:05
this sounds like a one time task. Is that true? Or is it something that has to run repeatedly, maybe in production? If it is a one-time thing, you can use the above code (with some changes) to generate more SQL, and then run the generated SQL. — MJB, Jan 13 '12 at 22:32
Yes it is a one time query, and the solution I ended up using was essentially this two step process. Still, it would be nice to know how to do it "properly". — Odalrick, Jan 15 '12 at 14:39

Bohemian · Accepted Answer · 2012-01-14T00:21:11.157

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.

And then the query could be instantly executed according to [this question](http://stackoverflow.com/questions/999200/is-it-possible-to-execute-a-string-in-mysql). Thank you. Still I'm wary of anything that is similar to eval. — Odalrick, Jan 15 '12 at 14:53

Copy table data with common columns

1 Answers1