Issue
I create a new db (usuarios) and i want to insert old values into a new table but my SQL backup also saved the auto_increment value and it is the primary key so when i import the file an error message show and it is due to the primary key value is already used in the new table, for instance:
INSERT INTO `usuarios` VALUES
(5, 'USERBETA', 'USERINFO')
It says that the primary key 5 is duplicated. How can I fix this to ignore the old auto_increment value and use the a new one instead?
Solution
UPDATE: This query will change your existing data to use much larger ID numbers, at which point you could then import the old data. There should be no ambiguity as long as you move existing table data IDs to a large enough number, and then import the old data.
UPDATE usuarios SET id = (id+10000)
(10000 is arbitrary, it should be some number larger than the largest existing ID in old data)
Then re-set your ID number at 1:
ALTER TABLE `usuarios` AUTO_INCREMENT = 1;
Then import your data.
ORIGINAL ANSWER
If you don’t have relational tables (nothing depending on the fixed ID’s already set in table usuarios) then you can just import without the id column. But, if you need to keep those old id already assigned, you could turn off auto_increment before import and then re-enable once you’ve inserted everything.
Presuming id is the column of your id:
Remove any existing (old) data:
TRUNCATE TABLE usuarios
Remove the auto_increment
ALTER TABLE usuarios CHANGE `id` INT(11) NOT NULL
Import your data like you have been.
Then, re-add the auto_increment:
ALTER TABLE usuarios MODIFY `id` INTEGER NOT NULL AUTO_INCREMENT;
ALTER TABLE `usuarios` AUTO_INCREMENT = 1234;
Answered By – Patrick Moore
Answer Checked By – David Marino (BugsFixing Volunteer)