Jonathan Jonathan - 4 months ago 9
SQL Question

INSERT INTO ... SELECT without detailing all columns

How do you insert selected rows from

table_source
to
table_target
using SQL in MySQL where:


  • Both tables have the same schema

  • All columns should transfer except for the auto-increment
    id

  • Without explicitly writing all the column names, as that would be tedious



The trivial
INSERT INTO table_target SELECT * FROM table_source
fails on duplicate entries for primary key.

Answer

Either you list all of the fields you want in the insert...select, or you use something else externally to build the list for you.

SQL does not have something like SELECT * except somefield FROM, so you'll have to bite the bullet and write out the field names.

Comments