Sqlite – How to merge many SQLite databases

databasesqlite

If I have a large number of SQLite databases, all with the same schema, what is the best way to merge them together in order to perform a query on all databases?

I know it is possible to use ATTACH to do this but it has a limit of 32 and 64 databases depending on the memory system on the machine.

Best Answer

To summarize from the Nabble post in DavidM's answer:

attach 'c:\test\b.db3' as toMerge;           
BEGIN; 
insert into AuditRecords select * from toMerge.AuditRecords; 
COMMIT; 
detach toMerge;

Repeat as needed.

Note: added detach toMerge; as per mike's comment.

Related Solutions

Sql – How to list the tables in a SQLite database file that was opened with ATTACH

There are a few steps to see the tables in an SQLite database:

List the tables in your database:
```
.tables
```
List how the table looks:
```
.schema tablename
```
Print the entire table:
```
SELECT * FROM tablename;
```
List all of the available SQLite prompt commands:
```
.help
```

Sqlite – How to check in SQLite whether a table exists

I missed that FAQ entry.

Anyway, for future reference, the complete query is:

SELECT name FROM sqlite_master WHERE type='table' AND name='{table_name}';

Where {table_name} is the name of the table to check.

Documentation section for reference: Database File Format. 2.6. Storage Of The SQL Database Schema

This will return a list of tables with the name specified; that is, the cursor will have a count of 0 (does not exist) or a count of 1 (does exist)

Best Answer

Related Solutions

Sql – How to list the tables in a SQLite database file that was opened with ATTACH

Sqlite – How to check in SQLite whether a table exists

Related Topic