Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to Merge Multiple Database files in SQLite?

I have multiple database files which exist in multiple locations with exactly similar structure. I understand the attach function can be used to connect multiple files to one database connection, however, this treats them as seperate databases. I want to do something like:

SELECT uid, name FROM ALL_DATABASES.Users;

Also,

SELECT uid, name FROM DB1.Users UNION SELECT uid, name FROM DB2.Users ;

is NOT a valid answer because I have an arbitrary number of database files that I need to merge. Lastly, the database files, must stay seperate. anyone know how to accomplish this?

EDIT: an answer gave me the idea: would it be possible to create a view which is a combination of all the different tables? Is it possible to query for all database files and which databases they 'mount' and then use that inside the view query to create the 'master table'?

like image 396
chacham15 Avatar asked Feb 06 '11 12:02

chacham15


People also ask

How do I merge two SQLite databases in Python?

In SQLiteStudio, you may simply load two DBs, and drag and drop a table from one DB into another, it will get saved automatically.

Can SQLite have multiple databases?

A lesser known feature of SQLite is that you can run queries, including joins, across tables from more than one database. The secret sauce is the ATTACH DATABASE command. Run the following SQL: ATTACH 'other.

How do I merge two tables in SQLite?

Syntax. The syntax for the SQLite CROSS JOIN is: SELECT columns FROM table1 CROSS JOIN table2; NOTE: Unlike an INNER or OUTER join, a CROSS JOIN has no condition to join the 2 tables.


1 Answers

Because SQLite imposes a limit on the number of databases that can be attached at one time, there is no way to do what you want in a single query.

If the number can be guaranteed to be within SQLite's limit (which violates the definition of "arbitrary"), there's nothing that prevents you from generating a query with the right set of UNIONs at the time you need to execute it.

To support truly arbitrary numbers of tables, your only real option is to create a table in an unrelated database and repeatedly INSERT rows from each candidate:

ATTACH DATABASE '/path/to/candidate/database' AS candidate;
INSERT INTO some_table (uid, name) SELECT uid, name FROM candidate.User;
DETACH DATABASE candidate;
like image 107
Blrfl Avatar answered Oct 11 '22 02:10

Blrfl