Append content of one table to another

carloslaso
carloslaso used Ask the Experts™
on
How do I append content of one table to another? They don't identical  structure but they have the same number of fields that I need to merge.

I'm tryng to do it using SSH to the server.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Distinguished Expert 2017

Commented:
are the tables within the same database or are you doing it accross databases as well as accross tables?


mysql -u root -p

insert into <newtable> (select column1,column2, column3,... from old table);

Author

Commented:
Is that going to copy the information into the columns? All of the columns on table1 exist on table1 on the other database but has more columns.

I need to copy the information that exist on table1 to table1 on the other database and leave the columns that do not exist on the other database blank.
Distinguished Expert 2017

Commented:
http://dev.mysql.com/doc/refman/5.0/en/insert-select.html

Your best bet might be to export the data from one and then import it into the other.

Are you familiar with perl or anyother programming language?

The columns you select will have to match the order and datatype of the one into which you are inserting.
you could specify the column order
insert into database.table (database.column1,......)  (select (1column, 2column, .....) from database2.table where );

Author

Commented:
The order and the datatype match but the table on one database has 24 fields, the table on the other database has 29 fields, the 5 extra fields are at the bottom/end of the table.

No, I'm not familiar with any programming languages :-/
Distinguished Expert 2017
Commented:
Your select statement needs to use the columns in the order in which they exist in the table into which you want the data added.
http://ariejan.net/2008/11/27/export-csv-directly-from-mysql
You can then do the reverse to import
http://dev.mysql.com/doc/refman/5.1/en/load-data.html

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial