Primary key conflict when merging databases (auto-increment)

I have two SQLite databases which I would like to merge. I’m doing it by using the following commands:

ATTACH "c:other.db" AS SecondaryDB
INSERT INTO MyTable SELECT * FROM SecondaryDB.MyTable

The problem is that MyTable has a primary column id which is auto-incremented in both databases. Thus, there is a primary key conflict.

How can I insert the rows from the secondary database such that auto-increment is used for the id column starting from the last value of the first database?

Answer

You want to copy all rows from the source, but without the auto-incremented column.

Just enumerate the columns in the insert and select clauses – all, expected the primary key column. SQLite will automatically assign new values to the auto-incremented column.

Say that the columns of your table is id, col1, col2, col3, you would do:

ATTACH "c:other.db" AS SecondaryDB
INSERT INTO MyTable(col1, col2, col3) 
SELECT col1, col2, col3 FROM SecondaryDB.MyTable