does this awesome sounding temp table then merge instantly when you commit it even if it?s huge?
> On Apr 14, 2015, at 1:01 PM, Scott Hess <shess at google.com> wrote: > > 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
