Import table into existing table using phpMyAdmin

Question

When I do this it makes a NEW table yet I've already selected the table to import to. I've tried CSV and ODS formats, still get the same results. The first column in my existing table is ID auto incremented. I've tried putting in a blank column (in the file I'm importing) to compensate and I've also tried it without putting the extra column in. Am I missing something simple here?

Playreader

Answer 1

First of all, I'm new to this, but just by fooling around trying to figure out how to accomplish something similar, and getting it to work, it's hard to believe the answers on here.

We're talking about phpMyAdmin here, which has endless options. You COULD choose to export the origin table with options - renaming the database and table in the process - telling someone to rename an existing database or table is irresponsible at best. No need for it.

Plus, nobody seems to be mentioning that what we really want to do is import COLUMNS into an existing table - the table is the same in every way, except perhaps the table and database name, right?

Here's what I did: I did a simple export of the origin table, which is identical, except in name, as the target table. I opened the sql file in SQL Management Studio (free from MS, and you can also use something like TextPad to do the same thing) and deleted all the stuff in the beginning about creating database, tables, etc. (put to where it says "INSERT" - leave that). I then replaced any instance of the orgin table name with the existing target table name. You will then seen in that sql file all the columns of data. Delete everything after that last column you want to import. Nothing else needed. Save that file with the name of the target table name. Import that sql file - nothing fancy needed. Done.

NOTE that if you already have existing columns in the target table, you'll error out on the first column because you have a primary key that's the same in the target as it's trying to use in the origin columns. I honestly don't know how to fix that in an elegant way. I had 7 columns already (primary keys 1-7) in the target table so I just cut those columns when editing the sql file, pasted at the end of the column list, and changed the keys to be in sequence from the last one. You also have to change the previous end of the last line from ; to , and then the new last line from , to ; Hopefully if you have to do that someone with a lot more experience can suggest a better way to do that - like ignore the imported keys and auto-increment as it imports. Or a better way to export so that that part gets taken care of automatically on import. Like I mentioned earlier, there's a TON of export options, including being able to fix the database and table names during export. I'm sure there's also something about primary key auto-incrementation.

BTW, when you say "select table" you mean clicked on it on the table list on the left, and then clicked the import button on the right? I thought that meant something to, if that's what you meant. It means nothing. The import button imports an sql script - that's it. What happens, where it goes, etc. is entirely up to the script itself.

Hope this helps someone.

Narine

Answer 2

You can certainly import one table to another table in Phpmyadmin.

1. Normalize the two tables so the structure matches
2. Rename the table to you wish to export to match the one imported to
3. Go ahead export/import in normal SQL, merge happens automatically

I just went through the steps myself successfully, imported couple of columns individually selected from a table in Phpmyadmin 3.3.2 and tried to import them to a table in Phpmyadmin 3.5.1, at first all that happened was that the old table was replicated into the new DB but this was solved as soon as I renamed it to fit the name of the new table (and normalized the structure).

Dela

This does not seem to be true (maybe a later version thing? I'm using 4.5.2 of PMA). Exporting from 1 MySQL database to another (dev to beta -> want to add records from 'table' in dev to those already in 'table' in beta). I export 'table' from dev, creating 'table.SQL' file. I import into 'table' in beta and get error: #1050 - Table 'table' already exists ... this happens whether I export the entire table or only specific rows – Corabelle

Answer 3

You cannot import one table to another table in phpmyadmin.
If you will try to do this it will create new tables in your database.
For more help you can refer this: IMPORTING DATABASES AND TABLES WITH PHPMYADMIN

Irinairis

Your reference says to delete the old table, then import the data. Fine. But this doesn't imply that importing into the old table (without deleting it) is not possible. – Glochidiate

Answer 4

You can import to an existing table from an excel file as long as you name your worksheet the same as your table name.

So if I was trying to import into my "owners" table, I would name my worksheet in Excel "owners".

Viviparous

Answer 5

if you download the import from another phpMyAdmin database. While downloading the table from custom, Format Specific options you can choose to have only data (with no structure). Then it will be imported with no problems.

For other imports you will have to modify the data manually to have no structural properties.

Cinch

Answer 6

There is a simple way I found out for importing data in your table without the need of deleting the entire table. Once you export the table as SQL file from another database, open it in your editor and copy the INSERT INTO part, paste it in the SQL section in the phpMyAdmin dashboard and execute it. You should make sure there is no duplicate id (otherwise it will give error saying that is already exists) or delete all the rows in that table and execute.

Infantryman