18

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.

subiet
  • 1,399
  • 1
  • 12
  • 18
  • 1
    http://stackoverflow.com/questions/80801/how-can-i-merge-many-sqlite-databases, http://stackoverflow.com/questions/3689694/merge-sqlite-files-into-one-db-file-and-begin-commit-question, http://stackoverflow.com/questions/3232900/how-to-merge-n-sqlite-database-files-into-one-if-db-has-the-primary-field, http://stackoverflow.com/questions/4913369/how-to-merge-multiple-database-files-in-sqlite, http://stackoverflow.com/questions/9048711/merging-databases, ... Nothing in there or linked questions helped you? – Mat Feb 19 '12 at 13:53

3 Answers3

28

here is one way to merge two database with all tables of the same structure. I hope it could help.

import sqlite3
con3 = sqlite3.connect("combine.db")

con3.execute("ATTACH 'results_a.db' as dba")

con3.execute("BEGIN")
for row in con3.execute("SELECT * FROM dba.sqlite_master WHERE type='table'"):
    combine = "INSERT INTO "+ row[1] + " SELECT * FROM dba." + row[1]
    print(combine)
    con3.execute(combine)
con3.commit()
con3.execute("detach database dba")
kritinsai
  • 23
  • 9
cheng chen
  • 479
  • 1
  • 4
  • 6
14

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 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.

ccpizza
  • 28,968
  • 18
  • 162
  • 169
  • 1
    Hey, GUI isn't an option, this has to be done withing a program, repeatedly. I will explore the SQL dump method, will it take care of primary key constraint on ID in each table, I am bit doubtful on that. – subiet Feb 20 '12 at 06:14
  • 2
    Why do you need to export the primary keys? Export the data and insert the records, the keys will be generated for you. If you want to do it in Python then have a look at http://mysql-python.sourceforge.net/MySQLdb.html#some-examples and http://www.kitebird.com/articles/pydbapi.html – ccpizza Feb 20 '12 at 12:57
  • 1
    In SQLiteStudio, you may simply load two DBs, and drag and drop a table from one DB into another, it will get saved automatically. – Wiktor Stribiżew Jan 18 '21 at 13:59
  • First solution gives me error because of "CREATE TABLE" (both files have the same schema). – Felix Apr 19 '23 at 08:44
0

Assuming you want to merge a db called OLD.DB into a db called NEW.DB, And assuming the have the same columns,

sqlite3 old.db .dump|tail -n +7 |sqlite3 new.db &>/dev/null
Zibri
  • 9,096
  • 3
  • 52
  • 44