sqlite copy data from one table to another

INSERT INTO Destination SELECT * FROM Source;

See SQL As Understood By SQLite: INSERT for a formal definition.

If you have data already present in both the tables and you want to update a table column values based on some condition then use this

UPDATE Table1 set Name=(select t2.Name from Table2 t2 where t2.id=Table1.id)

I've been wrestling with this, and I know there are other options, but I've come to the conclusion the safest pattern is:

create table destination_old as select * from destination;

drop table destination;

create table destination as select
d.*, s.country
from destination_old d left join source s
on d.id=s.id;

It's safe because you have a copy of destination before you altered it. I suspect that update statements with joins weren't included in SQLite because they're powerful but a bit risky.

Using the pattern above you end up with two country fields. You can avoid that by explicitly stating all of the columns you want to retrieve from destination_old and perhaps using coalesce to retrieve the values from destination_old if the country field in source is null. So for example:

create table destination as select
d.field1, d.field2,...,coalesce(s.country,d.country) country
from destination_old d left join source s
on d.id=s.id;