Dumping a table's content in sqlite3 to be imported into a new database

Question

Is there an easy way of dumping a SQLite database table into a text string with insert statements to be imported into the same table of a different database?

In my specific example, I have a table called log_entries with various columns. At the end of every day, I'd like to create a string which can then be dumped into an other database with a table of the same structure called archive. (And empty the table log_entries)

I know about the attach command to create new databases. I actually wish to add it to an existing one rather than creating a new one every day.

Thanks!

Answer 1

$ sqlite3 exclusion.sqlite '.dump exclusion' 
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE exclusion (word string);
INSERT INTO "exclusion" VALUES('books');
INSERT INTO "exclusion" VALUES('rendezvousing');
INSERT INTO "exclusion" VALUES('motherlands');
INSERT INTO "exclusion" VALUES('excerpt');
...

Answer 2

A tool called QweryBuilder from www.werysoft.com will allow you to retrieve information from log_entries then turn those results into insert statements. All you have to do is switch data sources and execute. Look at the Criteria Query window. In your case, you may want to use the Create Table from Results button. It will ask you for a table name, you would enter archive. Then you just remove the create table statement that was added to the script. The alternative would be to use the Create Insert Statements from Results button and do a replace.

Answer 3

ATTACH "%backup_file%" AS Backup;
INSERT INTO Backup.Archive SELECT * FROM log_entries;
DELETE FROM log_entries;
DETACH Backup;

All you need to do is replace %backup_file% with the path to your backup database. This approach considers that your Archive table is already defined and that you are using the same database file to cumulate your archive.