Re: [sqlite] Copy tables from one memory database to another with no disk access?
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
Re: [sqlite] Copy tables from one memory database to another with no disk access?
Hi Sqlite , insert into disk.your_table select * from your_table; There is an error for insert into disk.your_table select * from your_table Always double the inserted rows (within disk yous_table) I use Sqlite 3.3.2b Shum www.mingyik.com - Original Message - From: "Christian Smith" <[EMAIL PROTECTED]> To: <sqlite-users@sqlite.org> Sent: Saturday, January 28, 2006 12:27 AM Subject: Re: [sqlite] Copy tables from one memory database to another with no disk access? 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. Thanks, -Randy Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
Re: [sqlite] Copy tables from one memory database to another with no disk access?
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. > >Thanks, > > >-Randy > > Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
Re: [sqlite] Copy tables from one memory database to another with no disk access?
> Is it possible to copy tables from one memory database to another > without going through an intermediate disk database? did you try the really obvious way? create table db2.yourtable(...) insert into db2.yourtable select * from db1.yourtable
[sqlite] Copy tables from one memory database to another with no disk access?
Hello, Is it possible to copy tables from one memory database to another without going through an intermediate disk database? Thanks, -Randy
