Fastest Way Merge Two SQLite Databases

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

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;

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: Merge tables from different databases with Primary Key Constraint

First attach the 3 databases:

ATTACH "all\tab_X.db" AS db1;
ATTACH "all\tab_Y.db" AS db2;
ATTACH "all\tab_Z.db" AS db3;

Create the new table:

CREATE TABLE db3.PAYMENT(Pmt_ID TEXT PRIMARY KEY, P_CUSTOMER_NAME TEXT, P_CASH_AMOUNT REAL);

Finally insert the rows:

INSERT INTO db3.PAYMENT(Pmt_ID, P_CUSTOMER_NAME, P_CASH_AMOUNT) 
SELECT 'X' || Pmt_ID, P_CUSTOMER_NAME, P_CASH_AMOUNT FROM db1.PAYMENT
UNION ALL
SELECT 'Y' || Pmt_ID, P_CUSTOMER_NAME, P_CASH_AMOUNT FROM db2.PAYMENT

---

Related Topics