0

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.

4 Answers 4

3

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

0
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;
      
1

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.

-3
INSERT INTO test_import_two (`name`, name1, name2) 
 (SELECT `name`, name1, name2 
   FROM test_import_one WHERE id = 2
 )
3
  • For same table INSERT INTO test_import_three (id1, name1, name2) (SELECT 216 ,name1, name2 FROM test_import_three WHERE id = 4) Dec 28, 2017 at 19:09
  • How is this different from the accepted answer?
    – dezso
    Dec 28, 2017 at 19:27
  • 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..
    – RDFozz
    Dec 28, 2017 at 19:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.