How can I merge many SQLite databases?

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.


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.

import sqlite3
import os


def merge_databases(db1, db2):
    con3 = sqlite3.connect(db1)

    con3.execute("ATTACH '" + db2 +  "' as dba")

    con3.execute("BEGIN")
    for row in con3.execute("SELECT * FROM dba.sqlite_master WHERE type='table'"):
        combine = "INSERT OR IGNORE INTO "+ row[1] + " SELECT * FROM dba." + row[1]
        print(combine)
        con3.execute(combine)
    con3.commit()
    con3.execute("detach database dba")


def read_files(directory):
    fname = []
    for root,d_names,f_names in os.walk(directory):
        for f in f_names:
            c_name = os.path.join(root, f)
            filename, file_extension = os.path.splitext(c_name)
            if (file_extension == '.sqlitedb'):
                fname.append(c_name)

    return fname

def batch_merge(directory):
    db_files = read_files(directory)
    for db_file in db_files[1:]:
        merge_databases(db_files[0], db_file)

if __name__ == '__main__':
    batch_merge('/directory/to/database/files')

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:

  1. Download Sqlitestudio
  2. Add all your database files by using the Ctrl + O keyboard shortcut
  3. Double-click each now-loaded db file to open/activate/expand them all
  4. Fun part: simply right-click on each of the tables and click on Copy, 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 Paste

I was wowed to realize that such a daunting task can be solved using the ancient programming skill called: copy-and-paste :)


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.