SQLite Forum

How to append one table of one database to another table of another database ?
Login

How to append one table of one database to another table of another database ?

(1) By anonymous on 2021-08-20 06:50:34 [link] [source]

I have a memory database as a buffer, and I'd like to periodically copy one of its table to the file database. The table schema of both file and mem db are identical.
I tried to use INSERT INTO fileDB.table (columns) SELECT (columns) from memDB.table. Although it worked well, but the performance was slow. Is there any other method for example copy data from pager layer that may improve the performance ? I looked the backup API, it seems only copy the full database to another file and make them the same. How to append one to another ?

(2) By Richard Hipp (drh) on 2021-08-20 07:22:49 in reply to 1 [source]

There is no other way. But if you formulate the query without specifying column names:

INSERT INTO fileDB.table SELECT * FROM memDB.table;

Then the xfer-optimization will kick in and help it to run faster.