Merging two tables (insert without primary key)

[laura203]

This is not a homework assignment. :)

I'm a MySQL newbie and we just bought a product that uses it. The vendor has indicated that what I want is a special request and wants to schedule professional services time (several weeks in the future) to write us a personalized script. It's affecting production, so I need to get something worked out sooner than that.

Situation: All changes made through the GUI are logged in a table auditlog. One type of activity is causing the table to fill up, preventing users from logging in. We did some selective pruning of the information (which is not an option through the vendor-supplied tools) and were able to get it back up and running. Shortly after I went on vacation for the holidays, another error appeared and a coworker truncated the auditlog.

It's much too late to roll back to that version of the database, but I am able to pull out the pieces of the auditlog that we would like to merge with what's been created since the table was truncated. I've installed MySQL on my machine, imported the backup copy, and cleaned up the table to just what we want to keep permanently.

So:
Local machine - auditlog contains historical records
Production machine - auditlog contains recent records

Issue: I can't do a straight import because the primary keys will have duplicates.

Question: How do I merge the information from the active auditlog and the records from the backup we want to keep?

[smantscheff]

If the auditlog table has a primary auto_increment key, you can insert the records into a new table by selecting all fields except the auto_increment field for which you insert a NULL.
Example:

insert into newtable (id, field2, field3, field4) select NULL, field2, field3, field4 from oldtable;

This leads into problems, though, if there are some relations which refer to the id field. So if this does not solve your problem post the table structure and the foreign key constraints or relations.

[laura203]

It's been a bit painful working with production data on my laptop, but I was finally able to merge the two fields successfully. :) Thanks for the help.