How can I merge many SQLite databases?

Question

If I have a large number of SQLite databases, all with the same schema, what is the best way to merge them together in order to perform a query on all databases?

I know it is possible to use ATTACH to do this but it has a limit of 32 and 64 databases depending on the memory system on the machine.

Answer 1

If you only need to do this merge operation once (to create a new bigger database), you could create a script/program that will loop all your sqlite databases and then insert the data into your main (big) database.

Answer 2

This would be done on demand, possible several times a day. The way I would see it working is as in http://www.nabble.com/Attempting-to-merge-large-databases-td18131366.html where the databases are merged into a large DB, the query performed and then the large database deleted.

Answer 3

With no offense, just as one developer to another, I'm afraid that your idea seems terribly inefficient. It seems to me that instead of uniting SQLite databases you should probably be storing several tables within the same Database file.

However if I'm mistaken I guess you could ATTACH the databases and then use a VIEW to simplify your queries. Or make an in-memory table and copy over all the data (but that's even worse performance wise, especially if you have large databases)

Answer 4

You can also use a diff/merge software to do the job. You can try SQLite Compare, it supports comparing and merging large SQLite tables among other things.

Good Luck

Liron