VInayK VInayK - 5 months ago 22
MySQL Question

How to 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 MS 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?

Answer

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;