Replace auto_increment id with next free ID when duplicate is detected

Question

I am trying to do a data migration from one table to another table within the same database. The problem is the records in the table I'm importing from, can have different data but contain the same auto increment id for the primary key so when I try to import it, it obviously says it can't write it due to a duplicate key.

I've tried doing the following query to get round this:

INSERT INTO my_table SELECT * FROM my_temp_table ON DUPLICATE KEY UPDATE my_table.LogID=LAST_INSERT_ID(my_table.LogID);

This query seemed to work and MySQL said it had written 10000 rows to the table, except it actually didn't.

How can I insert all the rows from my_temp_table but inserting with unique auto increment id?

UPDATE

A bit more info on the table as requested. Both tables are obviously identical definition. The table contains a LogID (This being the auto increment primary key) a timestamp and a few other fields. There was a problem where the database table contained some time stamps for the 1st to the 3rd July but not everything it should have.

There was an older database server which had the correct information so I did

CREATE TABLE my_temp_table LIKE my_table

Then imported the 1st to the 3rd july into this my temp table like so

INSERT INTO my_temp_table SELECT * FROM my_table WHERE Date BETWEEN '2017-07-01' AND '2017-07-03'

This was then mysqldump'ed and copied to the new server and imported.

On the new server I then deleted all records from the 1st to the 3rd July as follows:

DELETE FROM my_table WHERE Date BETWEEN '2017-07-01' AND '2017-07-03'

Now I need to get the data for the 1st to 3rd July from my temp_table into my_table so the database has all the information it needs between those two dates.

Because my_table was inserting other records across the cause of July and the old server was adding more data as it was working correctly for the first 3 days in July, the old server records have auto_increment IDs that clash with the new server so I need to import these rows from the old server, to the new server, but ensuring the log id is new so it can be inserted.

Answer 1

Presumably you don't care whether the rows in your new table have the same autoincrementing id value as the rows from which they came in your old table.

In that case, allow MySQL to generate the new id values, like this.

INSERT INTO my_table
            (col2, col3, col4, col5) 
     SELECT col2, col3, col4, col5
       FROM my_temp_table 

In your list of column names (col2, col3, col4, col5 in my example) don't mention the autoincrementing column. Then MySQL will generate its values correctly, but using a new sequence of values.

If you want the new autoincrementing sequence for these rows in a particular order, you can make that happen by using an ORDER BY clause.

Using LAST_INSERT_ID() the way you did in your example won't work.

Answer 2

If you need the primary keys to be corresponding to the timeline, means oldest entry --> lowest key then follow those steps.

I as a dev would never expect that if I select orderd by primary the entries would be in a chronological mess.

1. Incrase all the keys after the gap

   UPDATE my_table SET primary = primary + 1000 WHERE Date BETWEEN '2017-07-01' AND '2017-07-03'

2. Insert the temp entries with their key (or with key translation)

   INSERT INTO my_table SELECT * FROM my_temp_table

3. Incrase the auto increment value as described here

Answer 3

I would argue that the auto increment primary keys of the two source tables should not have anything to do with the auto increment column of the shared destination column. If you wish to retain the original primary keys, then do so but in a non unique column.