Hi,
D. Richard Hipp wrote :
Gerhard Haering wrote:
On Tue, Nov 02, 2004 at 12:37:23PM +0100, Oliver Bienert wrote:
Hello all,
I wonder if there is a possibility to save (dump) an in-memory database to file (and vice versa, loading from file) in a fast way? (Without having to create a new database and copying records by select and inserts)
There is none. Did you actually try INSERT ... SELECT and found the performance inadequate?
Use ATTACH to attach the file database to your in-memory database. Then do a
INSERT INTO file.table1 SELECT * FROM main.table1;
for each table.
I try to do this in a little test in C#; here is my code :
<code>
SQLiteConnection Conn = new SQLiteConnection();
Conn.ConnectionString = "Data Source=:memory:;New=True;Compress=True;Synchronous=Off";
Conn.Open();
SQLiteCommand Cmd = new SQLiteCommand();
Cmd = Conn.CreateCommand();
Cmd.CommandText = "CREATE table mp3Lst (mp3Id integer primary key, mp3File varchar(100))";
Cmd.ExecuteNonQuery();
SQLiteTransaction Transact;
ArrayList alMp3Lst = new ArrayList();
alMp3Lst.Add("1.mp3");
alMp3Lst.Add("2.mp3");
alMp3Lst.Add("3mp3");
alMp3Lst.Add("4.mp3");
alMp3Lst.Add("5.mp3");Transact = (SQLiteTransaction) Conn.BeginTransaction();
foreach (string mp3File in alMp3Lst)
{
Cmd.CommandText = String.Format("INSERT INTO mp3Lst (mp3File) VALUES ('{0}')", mp3File);
Cmd.ExecuteNonQuery();
}
Transact.Commit();
Cmd.CommandText = "ATTACH DATABASE database AS main";
Cmd.ExecuteNonQuery();
Cmd.CommandText = "INSERT INTO database.mp3Lst SELECT * FROM main.mp3Lst";
Cmd.ExecuteNonQuery();
</code>
As you can see, it's quite simple :).
I have an error after doing the ATTACH DATABASE, which is :
<error> SQL logic error or missing database: database main is already in use </error>
=> I'm new to sqlite so perharps I do a mistake in the ATTACH DATABASE method ?
Regards.
-- Didier BRETIN INFORMACTIS http://www.informactis.com/ tél : 04 72 69 52 00
