How are auto_increment keys handled in INSERT (SELECT * FROM...)

I have table1 and table2 in MySQL. Both have a primary auto_increment key id.

If the table schemas match and I do INSERT INTO table1 (SELECT * FROM table2) what happens with regards to the new rows inserted in to table1? Do they keep their old id values and generate conflicts when a row from table1 has the same id? Are new values generated by auto_increment? Does it depend on the storage engine or locking?

You can insert into an auto-increment column and specify a value. This is fine; it simply overrides the auto-increment generator.

If you try to insert a value of NULL or 0 or DEFAULT, or if you omit the auto-increment column from the columns in your INSERT statement, this activates the auto-increment generator.

So, it's fine to INSERT INTO table1 SELECT * FROM table2 (by the way, you don't need the parentheses). This means that the id values in table2 will be copied verbatim, and table1 will not generate new values.

If you want table1 to generate new values, you can't do SELECT *. Either you use null or 0 for the id column:

INSERT INTO table1 SELECT 0, col1, col2, col3, ... FROM table2;

Or else you omit the column from both the INSERT statement's column list and the SELECT statement's select-list:

-- No id in either case:
INSERT INTO table1 (col1, col2, col3) SELECT col1, col2, col3, ... FROM table2;

Before you ask, there is no syntax in SQL for "select * except for one column". You have to spell out the full list of column names you want to insert.

StackExchange Database Administrators Q#60521, answer score: 21