Copying rows between identiacal tables with auto increment ID's

I have 2 tables with identical structure and auto incremented开发者_如何学Go id's and I'm trying to copy rows from one to the other.

When I try to copy a row from table A to table B and the unique id clashes with one which already exists in table B, I would like to simply set the id of the row I'm copying in to whatever the next value is for the auto increment counter.

This is proving surprisingly difficult for me though. Here's what I have:

"INSERT INTO tableB SELECT * FROM tableA WHERE tableA.id = '$id'ON DUPLICATE KEY UPDATE tableB.id = LAST_INSERT_ID(tableB.id)"

That doesn't seem to be working for me though. Another thing is that I need to ensure that if the key does get changed, I need to be able to tell what the new key is so that I can make the necessary changes for rows in related tables which I'm also copying.

Can anyone point out what I'm doing wrong?

I realise there's some similar questions to this around here, I've read them all but I still can't seem to make sense of this.

Edit for background information: Since someone asked about why my setup is the way it is (and I wouldn't be surprised if it was sub-optimal). Basically I'm scraping some stuff off another website, through a bunch of string matching, into a DB. However, doe to the finicky nature of the scraping process, I don't like to run live queries on recently scraped data. So basically I scrape on a cron schedule, check the data integrity every so often and if it looks ok I copy it into the "live" tables on which queries are run.

OK Based on your update I would simply use the one table.

Add a field 'checked' default value 0 - instead of copying data you have decided is suitable simply update the field checked to 2. Update to 1 if its not usable.

Periodically delete those records with checked = 1.

Better still update to 1 when its OK and delete ones that are not immediately.

Use:

ON DUPLICATE KEY UPDATE tableB.id = tableB.id

That should then return the id of the record as the last insert id - even though its not been 'inserted' and keeps your referential integrity tip top...

now - what you are trying to do seems a little confusing to me (please feel free to explain further - it may get you a better answer).

Why do you need to copy all the records from a to b? are these tables meant to be 'identical'?