sql - Fastest Way merge two SQLITE Databases

Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Learn more about Collectives

Stack Internal

Knowledge at work

Bring the best of human thought and AI automation together at your work.

Explore Stack Internal

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.

stackoverflow.com/questions/80801/… , stackoverflow.com/questions/3689694/… , stackoverflow.com/questions/3232900/… , stackoverflow.com/questions/4913369/… , stackoverflow.com/questions/9048711/merging-databases , ... Nothing in there or linked questions helped you? Commented Feb 19, 2012 at 13:53 This question is similar to: How can I merge many SQLite databases? . If you believe it’s different, please edit the question, make it clear how it’s different and/or how the answers on that question are not helpful for your problem. user4157124 Commented Apr 11 at 18:26

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")
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.
With the command line sqlite3 utility create the SQL dumps:
sqlite3 database1.db .dump > dump1.sql
sqlite3 database2.db .dump > dump2.sql
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.
    4 Comments
 
 
 
 subiet
 subiet Over a year ago  
  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.
 2012-02-20T06:14:19.49Z+00:00     
    
 
 
  
 ccpizza
 ccpizza Over a year ago  
  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 mysql-python.sourceforge.net/MySQLdb.html#some-examples and kitebird.com/articles/pydbapi.html
 2012-02-20T12:57:48.24Z+00:00     
    
 
 
  
 Wiktor Stribiżew
 Wiktor Stribiżew Over a year ago  
  In SQLiteStudio, you may simply load two DBs, and drag and drop a table from one DB into another, it will get saved automatically.
 2021-01-18T13:59:03.03Z+00:00     
    
 
 
  
 Felix
 Felix Over a year ago  
  First solution gives me error because of "CREATE TABLE" (both files have the same schema).
 2023-04-19T08:44:24.887Z+00:00     
    
 
 
  
   
 

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
        Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.