Use a single query on all databases

Question

I need to execute one simple query on a very large number of databases (50 to be precise) at the same time

SELECT * FROM table1 WHERE column1 NOT IN(SELECT column1 FROM table2)

I am using:
SQLite Studio 3.1.0
and .sqlite databases

SQLite doesn't allow the use of DECLARE, so I can't use variables to store all the DB names

Database name structure:
commonpart_CUSTOM

UPDATE:
I realised I didn't explain my query. I would like to select all content from table1 where column1 doesn't match values contained in column1 from table2

I have to make this operation on 50 Databases all having the same structure but with different Data, instead of executing the query on one database at a time. I would like to be able to execute it on all my Databases with only one table for all the results merged but also declaring from which database it is coming from, though without having to write all the database names myself.

-Database1
  -Table1
     -Column1
     -Column2
     -Column3
     -Column4
  -Table2
     -Column1
     -Column2
     -Column3
  -Table3
     -Column1
     -Column2
     -Column3

-Database2
  -Table1
     -Column1
     -Column2
     -Column3
     -Column4
  -Table2
     -Column1
     -Column2
     -Column3
  -Table3
     -Column1
     -Column2
     -Column3

Column1 from table1
Contains data type declared in Column1 from table2 but in 1Million rows so it's duplicated

e.g.
row1 AAA
row2 AAA
row3 BBB
row4 FLM

Column1 from table2
Contains declared types of Data
e.g. Types: AAA, BBB, CCC, FFF

Expected Output:

╔════╦══════════════╦════════════╦════════════╦════════════╦════════════╗
║    ║   Database   ║   Column1  ║   Column2  ║   Column3  ║  Column4   ║
╠════╬══════════════╬════════════╬════════════╬════════════╬════════════╣
║  1 ║ Database1    ║    FLM     ║Data        ║Data        ║Data        ║
║  2 ║ Database2    ║     -      ║Data        ║Data        ║Data        ║
║  3 ║ Database3    ║    NULL    ║Data        ║Data        ║Data        ║
║  4 ║ Database4    ║    NULL    ║Data        ║Data        ║Data        ║
╚════╩══════════════╩════════════╩════════════╩════════════╩════════════╝

Column1 from Expected Output contains all Data Types not matching with the ones defined in Column1 from table2, The Database Column contains the name of the database from which the output is coming from, the other columns containg all the other that of the row where the not matching value was found

Since Column1 from table1 on row4 contains FLM which is not present in Column1 from table2 it is expected as output

I just need a way to tell my query to execute on multiple databases without writing the names of the databases myself, just like a loop.

NOTE: I am only able to provide Generic Data because it's work related, sorry.

Perhaps you can use SQLite `attach database` statement? https://sqlite.org/lang_attach.html — Arvo, Jun 15 '17 at 13:44
@Arvo Using attach database is a solution, but I would have to write all the database names myself, I want to still be able to use the query in case my database names change. That means generic/parametrized — Alexandru Martalogu, Jun 15 '17 at 14:12

score 4 · Accepted Answer · answered Jun 15 '17 at 20:50

The only way of accessing data in different databases is to ATTACH them. The default limit for attached databases is 10, so you have to compile your own version of the SQLite library in order to increase this limit. Once you have that, you can use a compound query over all fifty tables:

ATTACH '...' as db1;
ATTACH '...' as db2;
...

SELECT 'Database1' AS DB, * FROM db1.Table1 WHERE Column1 NOT IN (SELECT Column1 FROM db1.Table2)
UNION ALL
SELECT 'Database2'      , * FROM db2.Table1 WHERE Column1 NOT IN (SELECT Column1 FROM db2.Table2)
UNION ALL
...

SQLite is an embedded database and designed to be used from a 'real' programming language. Therefore, it is not possible to construct SQL statements dynamically from within SQL itself. Instead, you have to construct the query in your program.

score 0 · Answer 2 · answered Jun 15 '17 at 12:43

0

It's hard to give a specific answer not knowing the schema you're working with, but one thing you could try is using a view:

https://www.tutorialspoint.com/sqlite/sqlite_views.htm

answered Jun 15 '17 at 12:43

Ben Schroeder

346
2
7

I would like to avoid to write everything manually, but to use something automated instead, in case I want to use the same trick with other databases or a different amount. Also, if you could explain to me what do you mean by schema I could give you more information. – Alexandru Martalogu Jun 15 '17 at 13:09

Use a single query on all databases

2 Answers2