On Tue, Apr 14, 2015 at 9:37 AM, Jim Callahan <jim.callahan.orlando at gmail.com> wrote: > My recollection is that SQLite has a "temp" or "tmp" namespace available > for intermediate tables -- it was on my todo list, but I never got around > to exploring that option.
CREATE TEMP TABLE ... works very well for things like this. If you use a distinct table name you can just refer to it directly, or you can say temp.tablename (vs main.tablename). You can also create temp databases, where SQLite arranges for the underlying file to go away on close (POSIX deletes after opening, Windows uses DELETE-ON-CLOSE flags). I believe you pass NULL at the filename to sqlite3_open(). I haven't tried out whether you can also do "ATTACH NULL AS mydb", but I'd bet you can. The main advantage of temporary tables over a :memory: database is that you don't have to worry about blowing out memory, because it can spill to disk easily. There's no journal (because temporary), so it should be faster than a staging table in your database. SQLite also optimizes things if your temp tables can be handled entirely in the cache, they'll never hit disk (and even if they hit an OS write, I don't think they're ever sync'ed so the OS might never write things to disk). The main dis-advantage is that you can't checkpoint things. If you crash or shutdown, when you start back up the data is gone and you have to start over. -scott
