copy a sqlite3 table preserving custom _id I'm trying to copy a table whose _id field is given by integers with some gaps, e.g. 1,2,5,7, presumably due to deleted messages.
(This is a toy-model for a Whatsapp database I'm manipulating.)

I'd like to copy the table preserving the labelling 1,2,5,7, as opposed to 1,2,3,4 that is given by AUTOINCREMENT below.

What is the correct way to do it?

CREATE TABLE messages (_id INTEGER PRIMARY KEY AUTOINCREMENT, key_remote_jid TEXT NOT NULL);
ATTACH 'msgstoreF.db' AS android;
INSERT INTO messages (key_remote_jid)
    SELECT key_remote_jid
    FROM android.messages;


I can achieve that by

CREATE TABLE messages (_id INTEGER PRIMARY KEY, key_remote_jid TEXT NOT NULL);
ATTACH 'msgstoreF.db' AS android;
INSERT INTO messages (_id, key_remote_jid)
    SELECT _id, key_remote_jid
    FROM android.messages;


but then such table is not included in sqlite_sequence.
Just copy the ID values over:

CREATE TABLE messages (_id INTEGER PRIMARY KEY AUTOINCREMENT, key_remote_jid TEXT NOT NULL);
ATTACH 'msgstoreF.db' AS android;
INSERT INTO messages (_id, key_remote_jid)
    SELECT _id, key_remote_jid
    FROM android.messages;


Please read the SQLite Autoincrement documentation. New ID values will not conflict with the manually inserted values. (And it is likely that you do not need to specifiy the AUTOINCREMENT keyword.)