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
 )

• Nirmal Sharma: For same table INSERT INTO test_import_three (id1, name1, name2) (SELECT 216 ,name1, name2 FROM test_import_three WHERE id = 4)
• András Váczi: How is this different from the accepted answer?
• RDFozz: It's different from the accepted answer in that the accepted answer specifies a NULL value for the columns that aren't in table1 (or test_import_two, here). However, it's the same as Jonathan Fite's answer, without the explanation. Also, seems to assume OP wants to copy one row, not the entire table, probably because the answer is copied over from another question..