SQLite Forum

Placeholder for table name when inserting data?
Login
Here's what tells us you're probably doing something wrong: you have multiple tables with the same structure.  To people who are experienced with SQL, this is a very obvious sign.  Not only does it mean that your programming will be inefficient, but it means that your software will take longer to run, that your database will take more space in storage.

## Examples of why doing it this way is bad

SQLite is extremely well optimized for searching through an index.  It does it very quickly, with a minimum of memory used.  However, the design of SQL assumes you will have a limited number of tables.  Because of this SQLite is not well optimized for searching through a long list of tables.  Every operation which includes a table name requires this search.  It takes far longer to search through a thousand table names than it does to search through a thousand values in an index.

SQLite's database file is based around pages.  Each page is assigned to a table or an index.  If a table or an index doesn't take up an entire page, then the unused part of the page is wasted.  Suppose your database holds data for a thousand stocks.  Then you will have a thousand pages partially unused.  This takes up valuable storage space, makes backups take longer, etc..

(The above section is slightly distorted and dramatically simplified for clarity.)

## Suggested new structure

If you have two or more tables with the same columns and the same indexes, those tables should be merged into one table.  You add an extra column to hold what was previously the table name.

This may require changing your primary key.  It looks like for your existing primary key was a date field.  It looks like you had a separate table for each stock.  So your new primary key should be (stock, date).  SQLite will ensure that each *combination of those two* is unique.

## How to proceed

Don't import your existing CSV files into many separate tables.  Or if you've already done so, drop those tables.  Instead import your existing CSV files into one big table, setting the extra column for the stock code as you do so.