Staggan Posted May 14, 2015 Share Posted May 14, 2015 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 Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted May 14, 2015 Share Posted May 14, 2015 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. Quote Link to comment Share on other sites More sharing options...
Staggan Posted May 14, 2015 Author Share Posted May 14, 2015 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... Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.