Auto Increment Question

[skipwater]

I have two db each have the same table called manufacturers and same fields.

db1 has a field called man_ID which is a auto increment field. This contains 90 records but over time the records have been added and deleted so the man_ID auto increment value is no longer in sequence.

db2 is new with no records.

What I need to do is insert the old manufactures records into the new db2 and keep the man_ID values of the old records.

This is the base code of what I have been editing to try and get it to work.

$sql = "INSERT INTO $manufacturers (man_ID, email, man_name) VALUES ('$man_ID', '$email', '$man_name')";

Any help would be appreciated

Skip

[Atli]

Have you tried the INSERT ... SELECT syntax?
Something like:

INSERT INTO newTable SELECT * FROM oldTable;

I would of course recommend trying this first on a table that you can afford to mess up if anything should go wrong.

[henryrhenryr]

Since it's a small table I would opt for the quick but dirty method of:

1. Dump the table (mysqldump DB TABLE > file.sql)

2. Open file.sql in a text editor with find+replace regexp (I use context)

3. Find "\('[0-9]+'," (that's the primary key - assuming it's the first column) and replace with "\("

4. Truncate the original table

5. Run the query - the DB will add the records with new primary keys...

I'm sure MySQL can achieve the same but if you're only doing it once...