How to Merge Multiple Database Files in SQLite

Fastest Way merge two SQLITE Databases

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

For GUIs have a look at http://www.sqlite.org/cvstrac/wiki?p=ManagementTools

For example, with SQLiteStudio that will be Database > Export the database: Export format: SQL > Done.

With the command line sqlite3 utility (available in linux repos and often already present ootb) you can create a dump with:

sqlite3 my_database.db .dump > mydump.sql

The SQL dump can be imported directly to a new/existing sqlite database from the shell with:

sqlite3 my_database.db < my_dump.sql

Merging multiple databases with relations in SQLite

I believe that you could base it on the following (instead of attaching the database, 2 has been appended to the 2nd set of table names (for convenience), additionally the data has been prefixed with C2 for the 2nd set of tables) :-

DROP TABLE IF EXISTS Name;
DROP TABLE IF EXISTS Name2;
DROP TABLE IF EXISTS Category;
DROP TABLE IF EXISTS Category2;

CREATE TABLE Category (Id INTEGER PRIMARY KEY NOT NULL UNIQUE,Category STRING);
INSERT INTO Category (Category, Id) VALUES ('B', 2), ('A', 1);

CREATE TABLE Name (Id INTEGER PRIMARY KEY UNIQUE NOT NULL, 
CatId INTEGER 
REFERENCES Category (Id) ON DELETE CASCADE ON UPDATE CASCADE MATCH SIMPLE, Name  STRING);
INSERT INTO Name (Name,CatId,Id)VALUES ('A',1,1),('AB',1,3 ),('B',2,2);

CREATE TABLE Category2 (Id INTEGER PRIMARY KEY NOT NULL UNIQUE,Category STRING);
INSERT INTO Category2 (Category, Id) VALUES ('C2B', 2), ('C2A', 1);

CREATE TABLE Name2 (Id INTEGER PRIMARY KEY UNIQUE NOT NULL, 
CatId INTEGER 
REFERENCES Category2 (Id) ON DELETE CASCADE ON UPDATE CASCADE MATCH SIMPLE, Name  STRING);
INSERT INTO Name2 (Name,CatId,Id)VALUES ('C2A',1,1),('C2AB',1,3 ),('C2B',2,2);

UPDATE Category2 SET id = id + (Max((SELECT max(id) FROM Category),(SELECT max(id) FROM Category2)));
UPDATE Name2 SET id = id + (Max((SELECT Max(id) FROM name) ,(SELECT max(id) FROM name2)));

SELECT * FROM Category2;
SELECT * FROM Name2;
INSERT INTO Category SELECT * FROM Category2 WHERE 1;
INSERT INTO name SELECT * FROM name2 WHERE 1;
SELECT * FROM Category;
SELECT * FROM Name;

• Note you mention AUTOINCREMENT but haven't included it, so checking for the highest sqlite_sequence value hasn't been included.
• The above relies upon the CASCADE On UPDATE, to cascade the increase to the Category.id down to the CatId.

This works by finding the highest id of both tables with the same schema and then updating the id's of the table to be merged by adding the found highest id to the id's of all rows. When the tables are the Category table the updated ID's are cascaded to the respective Name table.

The process is performed for both the pair of Category tables and the pair of Name tables.

The result (the last query is) :-

How to merge N SQLite database files into one if db has the primary field?

I'm not 100% sure, but it seems that I should read all the elements and insert the element (except the PRIMARY KEY) one by one into the new data base.

Sqlite merging databases into one, with unique values, preserving foregin key relation

PRAGMA foreign_keys = on;

ATTACH DATABASE 'db1.sqlite' AS db1;

ATTACH DATABASE 'db2.sqlite' AS db2;

BEGIN;

CREATE TABLE Fruit      (
                          id            INTEGER PRIMARY KEY NOT NULL,
                          name          TEXT    UNIQUE ON CONFLICT IGNORE
                          )
                          ;

CREATE TABLE Juice      (
                          id            INTEGER PRIMARY KEY NOT NULL,
                          name          TEXT    UNIQUE ON CONFLICT IGNORE
                        )
                        ;

CREATE TABLE Recipe     (
                          id            INTEGER PRIMARY KEY NOT NULL,
                          juice_id      INTEGER NOT NULL,
                          fruit_id      INTEGER NOT NULL,
                          FOREIGN KEY   ( juice_id ) REFERENCES Juice ( id )
                                        ON UPDATE CASCADE
                                        ON DELETE CASCADE,
                          FOREIGN KEY   ( fruit_id ) REFERENCES Fruit ( id )
                                        ON UPDATE CASCADE
                                        ON DELETE CASCADE
                        )
                        ;

INSERT INTO Fruit  ( id, name )               SELECT id, name FROM db1.Fruit;
INSERT INTO Juice  ( id, name )               SELECT id, name FROM db1.Juice;
INSERT INTO Recipe ( id, juice_id, fruit_id ) SELECT id, juice_id, fruit_id FROM db1.Recipe;

INSERT INTO Fruit ( name ) SELECT name FROM db2.Fruit;
INSERT INTO Juice ( name ) SELECT name FROM db2.Juice;

CREATE TEMPORARY TABLE Recipe_tmp AS
                                    SELECT Juice.name AS j_name, Fruit.name AS f_name
                                      FROM db2.Recipe, db2.Fruit, db2.Juice
                                        WHERE db2.Recipe.juice_id = db2.Juice.id AND db2.Recipe.fruit_id = db2.Fruit.id
;

INSERT INTO Recipe ( juice_id, fruit_id ) SELECT j.id, f.id
                                            FROM Recipe_tmp AS r, Juice AS j, Fruit AS f
                                              WHERE r.j_name = j.name AND r.f_name = f.name
;

DROP TABLE Recipe_tmp;

COMMIT;

DETACH DATABASE db1;
DETACH DATABASE db2;

---

Related Topics