Jonathan Jonathan - 8 months ago 25
SQL Question

INSERT INTO ... SELECT without detailing all columns

How do you insert selected rows from

using SQL in MySQL where:

  • Both tables have the same schema

  • All columns should transfer except for the auto-increment

  • 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.


Either you list all of the fields you want in the, 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.