<p>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? </p>

<p>I know it is possible to use <a href="http://www.sqlite.org/lang_attach.html" rel="noreferrer">ATTACH</a> to do this but it has <a href="http://www.sqlite.org/limits.html#max_attached" rel="noreferrer">a limit</a> of 32 and 64 databases depending on the memory system on the machine.</p>

[{'answer_id': 80812, 'author': 'Espo', 'author_id': 2257, 'author_profile': 'https://Stackoverflow.com/users/2257', 'pm_score': 1, 'selected': False, 'text': '<p>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.</p>\n'}, {'answer_id': 81488, 'author': 'Robert Gould', 'author_id': 15124, 'author_profile': 'https://Stackoverflow.com/users/15124', 'pm_score': -1, 'selected': False, 'text': "<p>With no offense, just as one developer to another, I'm afraid that your idea seems terribly inefficient.\nIt seems to me that instead of uniting SQLite databases you should probably be storing several tables within the same Database file.</p>\n\n<p>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)</p>\n"}, {'answer_id': 11089277, 'author': 'dfrankow', 'author_id': 34935, 'author_profile': 'https://Stackoverflow.com/users/34935', 'pm_score': 7, 'selected': False, 'text': '<p>To summarize from the <a href="https://web.archive.org/web/20120615034014/http://sqlite.1065341.n5.nabble.com/Attempting-to-merge-large-databases-td39548.html" rel="noreferrer">Nabble post</a> in DavidM\'s answer:</p>\n<pre><code>attach \'c:\\test\\b.db3\' as toMerge;           \nBEGIN; \ninsert into AuditRecords select * from toMerge.AuditRecords; \nCOMMIT; \ndetach toMerge;\n</code></pre>\n<p>Repeat as needed.</p>\n<p><em>Note: added <code>detach toMerge;</code> as per mike\'s comment.</em></p>\n'}, {'answer_id': 53313528, 'author': 'Damilola Olowookere', 'author_id': 1823554, 'author_profile': 'https://Stackoverflow.com/users/1823554', 'pm_score': 3, 'selected': False, 'text': '<p>Although a very old thread, this is still a relevant question in today\'s programming needs. I am posting this here because none of the answers provided yet is concise, easy, and straight-to-point. This is for sake of Googlers that end up on this page. GUI we go:</p>\n\n<ol>\n<li>Download <a href="https://sqlitestudio.pl/index.rvt?act=download" rel="noreferrer">Sqlitestudio</a>  </li>\n<li>Add all your database files by using the <code>Ctrl + O</code> keyboard shortcut</li>\n<li>Double-click each now-loaded db file to open/activate/expand them all</li>\n<li>Fun part: simply right-click on each of the tables and click on <code>Copy</code>, and then go to the target database in the list of the loaded database files (or create new one if required) and right-click on the target db and click on <code>Paste</code></li>\n</ol>\n\n<p>I was wowed to realize that such a daunting task can be solved using the ancient programming skill called: copy-and-paste :)</p>\n'}, {'answer_id': 61954182, 'author': 'Pedro Lobito', 'author_id': 797495, 'author_profile': 'https://Stackoverflow.com/users/797495', 'pm_score': 2, 'selected': False, 'text': '<p>Late answer, but you can use:</p>\n\n<pre><code>#!/usr/bin/python\n\nimport sys, sqlite3\n\nclass sqlMerge(object):\n    """Basic python script to merge data of 2 !!!IDENTICAL!!!! SQL tables"""\n\n    def __init__(self, parent=None):\n        super(sqlMerge, self).__init__()\n\n        self.db_a = None\n        self.db_b = None\n\n    def loadTables(self, file_a, file_b):\n        self.db_a = sqlite3.connect(file_a)\n        self.db_b = sqlite3.connect(file_b)\n\n        cursor_a = self.db_a.cursor()\n        cursor_a.execute("SELECT name FROM sqlite_master WHERE type=\'table\';")\n\n        table_counter = 0\n        print("SQL Tables available: \\n===================================================\\n")\n        for table_item in cursor_a.fetchall():\n            current_table = table_item[0]\n            table_counter += 1\n            print("-&gt; " + current_table)\n        print("\\n===================================================\\n")\n\n        if table_counter == 1:\n            table_to_merge = current_table\n        else:\n            table_to_merge = input("Table to Merge: ")\n\n        return table_to_merge\n\n    def merge(self, table_name):\n        cursor_a = self.db_a.cursor()\n        cursor_b = self.db_b.cursor()\n\n        new_table_name = table_name + "_new"\n\n        try:\n            cursor_a.execute("CREATE TABLE IF NOT EXISTS " + new_table_name + " AS SELECT * FROM " + table_name)\n            for row in cursor_b.execute("SELECT * FROM " + table_name):\n                print(row)\n                cursor_a.execute("INSERT INTO " + new_table_name + " VALUES" + str(row) +";")\n\n            cursor_a.execute("DROP TABLE IF EXISTS " + table_name);\n            cursor_a.execute("ALTER TABLE " + new_table_name + " RENAME TO " + table_name);\n            self.db_a.commit()\n\n            print("\\n\\nMerge Successful!\\n")\n\n        except sqlite3.OperationalError:\n            print("ERROR!: Merge Failed")\n            cursor_a.execute("DROP TABLE IF EXISTS " + new_table_name);\n\n        finally:\n            self.db_a.close()\n            self.db_b.close()\n\n        return\n\n    def main(self):\n        print("Please enter name of db file")\n        file_name_a = input("File Name A:")\n        file_name_b = input("File Name B:")\n\n        table_name = self.loadTables(file_name_a, file_name_b)\n        self.merge(table_name)\n\n        return\n\nif __name__ == \'__main__\':\n    app = sqlMerge()\n    app.main()\n</code></pre>\n\n<hr>\n\n<p>SRC :  <a href="https://github.com/kd8bny/sqlMerge" rel="nofollow noreferrer">Tool to merge identical SQLite3 databases</a> </p>\n'}, {'answer_id': 62630929, 'author': 'Taba', 'author_id': 8346770, 'author_profile': 'https://Stackoverflow.com/users/8346770', 'pm_score': -1, 'selected': False, 'text': '<p>If you have reached the bottom of this feed and yet didn\'t find your solution, here is also a way to merge the tables of 2 or more sqlite databases.</p>\n<p>First try to download and install <a href="https://sqlitebrowser.org/" rel="nofollow noreferrer">DB browser for sqlite database</a>. Then try to open your databases in 2 windows and try merging them by simply <strong>drag and drop</strong> tables from one to another. But the problem is that you can just drag and drop only <strong>one</strong> table at a time and therefore its not really a solution for this answer specifically but yet it can used to save some time from further searches if your database is small.</p>\n'}, {'answer_id': 68526717, 'author': 'Mohammadsadegh', 'author_id': 11586886, 'author_profile': 'https://Stackoverflow.com/users/11586886', 'pm_score': 3, 'selected': False, 'text': "<p>Here is a simple python code to either merge two database files or scan a directory to find all database files and merge them all together (by simply inserting all data in other files to the first database file found).Note that this code just attaches the databases with the same schema.</p>\n<pre><code>import sqlite3\nimport os\n\n\ndef merge_databases(db1, db2):\n    con3 = sqlite3.connect(db1)\n\n    con3.execute(&quot;ATTACH '&quot; + db2 +  &quot;' as dba&quot;)\n\n    con3.execute(&quot;BEGIN&quot;)\n    for row in con3.execute(&quot;SELECT * FROM dba.sqlite_master WHERE type='table'&quot;):\n        combine = &quot;INSERT OR IGNORE INTO &quot;+ row[1] + &quot; SELECT * FROM dba.&quot; + row[1]\n        print(combine)\n        con3.execute(combine)\n    con3.commit()\n    con3.execute(&quot;detach database dba&quot;)\n\n\ndef read_files(directory):\n    fname = []\n    for root,d_names,f_names in os.walk(directory):\n        for f in f_names:\n            c_name = os.path.join(root, f)\n            filename, file_extension = os.path.splitext(c_name)\n            if (file_extension == '.sqlitedb'):\n                fname.append(c_name)\n\n    return fname\n\ndef batch_merge(directory):\n    db_files = read_files(directory)\n    for db_file in db_files[1:]:\n        merge_databases(db_files[0], db_file)\n\nif __name__ == '__main__':\n    batch_merge('/directory/to/database/files')\n</code></pre>\n"}]
['https://Stackoverflow.com/questions/80801', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/2183/']