Insert data from a source table into a destination table with more columns?

Question

I want to use INSERT INTO SELECT * to insert data into a dest table from a source table, but I have some extra columns added to destination table. These columns are distributed around the table like 2 are at top and 2 are at bottom.

Also for insertion I can insert some default value like null to these columns.

Answer 1

SELECT * selects col1, col2, col3, as defined in table2, and if the selection doesn't match the target, it will fail.

the conclusion is to alter the selection:

INSERT INTO table1 
SELECT col1, col2, NULL as col_extra, 'default' as col_extra2, col3, [...] 
FROM table2

not tested, but should work

Answer 2

If the destination table has more columns than the source table you have some options

• Provide a value for the column which the source table lacks. This is the only option if the column is NOT NULL and lacks a DEFAULT

  INSERT INTO dest ( foo,bar )
    SELECT foo, 1 FROM src;    -- 1 is a literal, could also be f(foo)
  

• Either,

  • if the excess columns in the destination table has a DEFAULT, use it.

    INSERT INTO dest ( foo )
      SELECT foo FROM src;
    

    This is the same as the more explicit,

    INSERT INTO dest ( foo,bar )
      SELECT foo, DEFAULT FROM src;
    

  • Without a DEFAULT on the column, the column defaults to NULL. That's valid for all NULLABLE columns

    INSERT INTO dest ( foo )
      SELECT foo FROM src;
    

    This is the same as the more explicit,

    INSERT INTO dest ( foo,bar )
      SELECT foo, null FROM src;
    

Answer 3

You need to use the full syntax in order to make this work.

INSERT INTO table2 (<COLUMN NAMES>,...) SELECT <COLUMN NAMES>... FROM table1

Just list the columns to insert into and the ones you are selecting in the same order and you are good to go.

Answer 4

INSERT INTO test_import_two (`name`, name1, name2) 
 (SELECT `name`, name1, name2 
   FROM test_import_one WHERE id = 2
 )