Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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.

like image 346
EmmyS Avatar asked Sep 30 '10 19:09

EmmyS


1 Answers

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.

like image 121
Jason McCreary Avatar answered Jan 03 '23 18:01

Jason McCreary