How to join records from a table distributed in multiple . sqlite files?

Question

I have several files with extension .sqlite, containing every 1 at least 1000 records thus totaling about 4,000 records. See how they are distributed:

• vihicles1.sqlite
• vihicles2.sqlite
• vihicles3.sqlite
• vihicles4.sqlite

Within each file has a single table with the name tblVehicle containing the following columns:

• id (integer)
• make (text)
• model (text)
• year (int)

I’m using the Sqlitestudio for database management. It is possible to join these files by creating only 1 file with all records?

Answer 1

Attach the files in the session, give a INSERT selecting table data in each database. The base name (table surname) comes before the table.

ATTACH 'path/aqui/vihicles1.sqlite' as v1;         
ATTACH 'path/aqui/vihicles2.sqlite' as v2;
ATTACH 'path/aqui/vihicles3.sqlite' as v3;
ATTACH 'path/aqui/vihicles4.sqlite' as v4;

INSERT INTO tblVehicle SELECT * FROM v1.tblVehicle;
INSERT INTO tblVehicle SELECT * FROM v2.tblVehicle;
INSERT INTO tblVehicle SELECT * FROM v3.tblVehicle;
INSERT INTO tblVehicle SELECT * FROM v4.tblVehicle;

DETACH 'path/aqui/vihicles1.sqlite';
DETACH 'path/aqui/vihicles2.sqlite';
DETACH 'path/aqui/vihicles3.sqlite';
DETACH 'path/aqui/vihicles4.sqlite';

I put in the Github for future reference.

Don’t think this case is too necessary because of the low volume, but some settings may be useful to improve performance:

Some examples that may help:

PRAGMA synchronous = OFF
PRAGMA journal_mode = MEMORY

This does not create indexes.

You can use one of the files to receive the others, then the indexes already exist. But the import can be much slower. I don’t know how is the optimization of Sqlite, but in the past it was faster to import without index and then create the index.

It would be good to test some situations and see how it works best for you.