Search code examples
mysqlsqlcopyinsert-select

How to copy a table and add a field with MySQL?

I would like to copy a table A into a table B with INSERT ... SELECT without declaring all fields in MySQL.

But the issue is my table B has an auto-increment field at the beginning called id:

table_A

 name    age      etc
-------|--------|--------|
 abc   | 28     | ...    |

table_B

 id      name    age      etc
-------|-------|--------|--------|

I tried to do something like: 

INSERT INTO table_B SELECT auto, * FROM table_A

But it didn't work. What is the best solution?

Solution

  • Specify the column names explicitly:

    INSERT INTO table_B (name,age,etc) SELECT * FROM table_A

    or even

    INSERT INTO table_B (name,age,etc) SELECT name,age,etc FROM table_A