sqlsqlite

Fastest Way merge two SQLITE Databases

I have 3 SQLite DBs, each having exactly the same set of 7 tables with respect to table structure. [They are Log Dumps from 3 different Machines].

I want to combine them into one SQLite DB, having those very same 7 tables, but each table should have the combined data from all the three DBs. since I want to run queries across the 3 of them. What is the best, fastest way to do it.

Solution

  • Export each database to an SQL dump and then import the dumps into your new combined database.

    Using console sqlite3

    sqlite3 is available in most linux repos and usually already present ootb on linux and macos.

    1. With the command line sqlite3 utility create the SQL dumps:
    sqlite3 database1.db .dump > dump1.sql
sqlite3 database2.db .dump > dump2.sql
    1. Import the dumps to a new or existing sqlite database:
    sqlite3 merged_database.db < dump1.sql
sqlite3 merged_database.db < dump2.sql

    Using cli sqlite3 with custom columns/tables

    To include/exclude specific columns you can run a custom SELECT and dump the results to a file or pipe:

    # export specific columns
sqlite3 database1.db ".output dump1.sql" ".mode sql"  "SELECT lastname, firstname, department, position FROM employees;" ".exit"
# ... rinse and repeat for additional databases

# import
sqlite3 merged_database.db  ".mode sql"  ".import dump1.sql employees" ".exit"
# ... repeat for additional dumps

    At the time of writing sqlite3 supports 14 output formats beyond sql which include csv, json, tabs, markdown, etc.

    Using sqlite GUI's

    For available GUIs have a look at https://www2.sqlite.org/cvstrac/wiki?p=ManagementTools

    For example, to make a dump with DB Browser for SQLite on the menu pick File > Export > Database to SQL file.

    NOTE: With GUI tools you have more flexibility in terms of picking which columns to include, e.g. for example, in some cases, you might want to exclude primary/foreign keys, etc.