Jump to content

Append data from tables in one database to another


Staggan

Recommended Posts

Hello

 

I have multiple copies of a database schema, all with separate data... 

 

I now want to merge all of this data into one location...

 

I have prepared all the individual databases so there is no clash of any unique ID's etc... but I cannot find anything that will allow me to append the data from one database to the other...

 

For example..

 

I have a table called user and another called purchases... this table exists in each database... But now I want to merge that data into a single place...

 

I know I can do it table by table, but that is slow and if anything goes wrong it could take a long time to resolve...

 

Are there any automated tools I can use ?

Thanks

 

Link to comment
Share on other sites

how many different databases? 5, 10, 100? there are no wild-card ALL database commands.

 

to do this under program control would require that you get (from the schema data) or manually make a list of the databases with the tables you want to use as the source, then run a query for each source database that inserts into the destination database using an INSERT ... SELECT query.

 

the mysql documentation for the INSERT ... SELECT query states it can be used to insert from multiple tables, which should include (untested) the ability to specify the database.table. assuming this will work for a UNION query, you could build the SELECT part of the INSERT ... SELECT to be a UNION query between all the database.tables that you need.

 

to do this manually, the most efficient way i can think of would be to do .sql backups of all the source databases/tables that you need. then edit them so that the USE database commend points to the destination database. then simply import the data using the .sql files.

Link to comment
Share on other sites

Hello

 

There are currently 5 databases....

 

I thought MySQL used to allow a restore that was additive...  as in would append records to existing tables, if those tables existed... 

 

Ideally I want something that I can just name dbase 1 as source and dbase 2 as destination....  and add dbase 3 to that result...

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.