databasesqlitesqlitestudio

Use a single query on all databases

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.

Solution

  • 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.