Search code examples
mysqlauto-incrementmysql-workbench

Insert values into auto identity column in MySQL

I would like to insert values into MySQL innodb table Auto_Increment column.

I am loading some data from an old table to new table which has an identity, and need to preserve the existing values from the old table, so I need to preserve the existing Id values, but keep the column Auto_Increment for new values.

In SQL Server T-SQL, I would start my insert query script with SET SET IDENTITY_INSERT MyTable ON and end the query with SET SET IDENTITY_INSERT MyTable OFF.

How can I do the same in MySQL?

Solution

  • Just do as usual:

    INSERT INTO my_table(auto_inc_field, other_field) VALUES(8547, 'some value');

    If the values are comming from another table, you may use:

    INSERT INTO my_table(auto_inc_field, other_field)
SELECT auto_inc_field, other_field FROM other_table;