how can I ignore id column when importing into mySQL via phpMyAdmin?

0 votes
asked Sep 30, 2010 by emmys

I need to export data from a table in database A, then import it into an identically-structured table in database B. This needs to be done via phpMyAdmin. Here's the problem: no matter what format I choose for the export (CSV or SQL) ALL columns (including the auto-incremented ID field) get exported. Because there's already data in the table in database B, I can't import the ID field with the new records - I need it to import the records and assign new auto-incremented values to the records. What settings do I need to use in either the export (to be able to choose which columns to export) or the import (to tell it to ignore the ID column in the file)?

Or should I just export as CSV, then open in Excel and delete the ID column? Is there a way to tell phpMyAdmin that it should generate new auto-incremented IDs for the records being imported, without it telling me that there's an incorrect column count in the import file?

EDIT: to clarify, I'm exporting only data, not structure.

2 Answers

0 votes
answered Sep 30, 2010 by jason-mccreary

Excel is an option to remove the column and probably the fastest at this point.

But if these databases are on the same server and you have access you can just to an INSERT INTO databaseB.table (column_list) SELECT column_list FROM databaseA.table.

You can also run the SELECT statement to just get the desired columns and then export the results. This link should be available in the recent versions of PHPMyAdmin.

0 votes
answered Sep 15, 2017 by r-jones

It is several years since the original question, but this still came out top in a google search so I'll comment on what worked for me:

If I delete the Id column in my CSV and then try to import I get the 'Invalid column count in CSV input on line 1.' error.

But if I keep the Id column but change all of the Id values to NULL in excel (just typing NULL into the cell), then when I import this the id auto-increment fills in the new records with consecutive numbers (presumably starting with the highest existing record Id +1 ).

I'm using PHPMyAdmin 4.7.0

Welcome to Q&A, where you can ask questions and receive answers from other members of the community.
Website Online Counter