21

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?

ProfK
  • 49,207
  • 121
  • 399
  • 775
VInayK
  • 1,501
  • 6
  • 34
  • 47
  • Why? Is the value MySQL provides automatically not suitable? – Mike Sherrill 'Cat Recall' Apr 19 '13 at 15:22
  • Why is the column an Auto Identity if you need to insert into it? – DJ Burb Apr 19 '13 at 15:23
  • Auto-identity columns are filled in automatically by MySQL. If you want a different number, you should maintain a separate field. – Robert Harvey Apr 19 '13 at 15:23
  • 3
    i wanted to load some data from other table to new table which has an identity, so i want to use the existing values from old table. – VInayK Apr 19 '13 at 15:29
  • 14
    This is a pretty common need when loading sample data or moving data from one database to another. I really don't understand why this is marked as "not a question". – Jonathan Allen Jan 24 '19 at 22:00
  • 1
    Agree with the previous 2 comments. They are nieve and clearly not based on experience with migrating real data or building tests for development (that must be run on staging and production with the same data). – rainabba Jul 08 '20 at 19:26

1 Answers1

17

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;
Jonathan Allen
  • 68,373
  • 70
  • 259
  • 447
Jocelyn
  • 11,209
  • 10
  • 43
  • 60
  • 2
    And if you import data, there you go with a brand new id value that will generate if your integer in auto_inc_field is 0 or NULL. "violation of foreign key constraint xxx". This answer deserves being downvoted rather than the question... – Stefan Steiger Nov 12 '13 at 17:10
  • 2
    If anyone stumbles across this just like me, and you have inserted rows with an ID. You can manually set the auto_inc_field using 'ALTER TABLE test AUTO_INCREMENT = 10' - where 10 is the next value to be used for an inserted row. – Martin Blore Mar 02 '17 at 20:47
  • With PostgreSQL you likewise have to reset the auto-increment field. – Jonathan Allen Jan 24 '19 at 22:06
  • 1
    @MartinBlore Updated answer to this now 4 years later. From the documentation on version 5.7 `When you insert any other value into an AUTO_INCREMENT column, the column is set to that value and the sequence is reset so that the next automatically generated value follows sequentially from the largest column value.` – RubberChickenLeader May 14 '21 at 18:13