Menu
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.
413
To merge SQL database of the same server, enter the same server name as in Step 2. To merge SQL databases of different servers, enter the server name of that server (you can connect the machine on a network if the server resides on a different system).
Yes, SQLite explicitly supports multi-database transactions (see https://www.sqlite.org/atomiccommit.html#_multi_file_commit for technical details).
SQLite database files have a maximum size of about 140 TB. On a phone, the size of the storage (a few GB) will limit your database file size, while the memory size will limit how much data you can retrieve from a query. Furthermore, Android cursors have a limit of 1 MB for the results.
To summarize from the Nabble post in DavidM's answer:
attach 'c:\test\b.db3' as toMerge; BEGIN; insert into AuditRecords select * from toMerge.AuditRecords; COMMIT; detach toMerge; Repeat as needed.
Note: added detach toMerge; as per mike's comment.
158
This would be done on demand, possible several times a day. The way I would see it working is as in http://sqlite.1065341.n5.nabble.com/Attempting-to-merge-large-databases-td39548.html where the databases are merged into a large DB, the query performed and then the large database deleted.
29
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With