Christian Smith wrote:
On Wed, 25 Jan 2006, Randy Graham wrote:
Hello,
Is it possible to copy tables from one memory database to another
without going through an intermediate disk database?
No, but you can copy the tables to be backed up to a temporary table, then
backup up to disk from the temporary table. The temporary table will be a
snapshot of your table at a point in time.
As the temporary table will be in a different tablespace (it'll use a
memory database or temporary database file, depending on temp_store
pragma) from the main database, writes can continue on the main memory
database once the temporary backup has been done, and the dump to disk db
can occur from the temporary table concurrently:
attach "disk.db" as disk;
create temporary table backup as select * from your_table;
begin;
delete from disk.your_table;
insert into disk.your_table select * from your_table;
commit;
drop temporary table backup;
If you have more than one table to backup, you might to want to create all
backup tables in a single transaction, then dump them out one after the
other to the disk db in the second transaction. Further, you may want to
create an explicit temporary memory database using attach, as the default
temporary database is also used for internal sorting, I believe.
Thank you Christian !
I should have scoured the docs again, I didn't realize that temp tables
reside in memory with temp_store=MEMORY.
I think I've finally solved my problem by using temp db files on RAM
disk. Fortunately my embedded system has lots of RAM and a RAM disk
mount, and initial tests are encouraging.
I'll also look at the temp table approach to see what advantages it
might offer.
Thanks again for your reply.
-Randy
