How to copy data between two tables in SQLite?

Question

I have two table with different column like this:

table1
(
    _id,
    title,
    name,
    number,
    address
)

table2
(
    _id,
    phone,
    name,
    address
)

How can I copy data 'name', 'address' from table1 to table2.

And my question have two situation:

First: table1, table2 in the same database file
Second: table1 in data1.db file, table2 in data2.db file

Aaron Digulla Aaron Digulla · Accepted Answer · 2009-10-13T12:19:05

Copying in SQL works like so:

insert into table2 (name, address)
select name, address
from table1

If the values of the column id_ are the same, you need to insert and update

insert into table2 (name, address)
select name, address
from table1 t1
where not exists (select * from table2 t2 where t1._id = t2._id)
;
update table2 t2 name = (select name from table1 t2 where t1._id = t2._id)
;
update table2 t2 address = (select address from table1 t2 where t1._id = t2._id)

If you need to copy the columns between databases, you first export them into a file (use any format you like, for example CSV) and then merge that file into the second database manually since you can't write an SQL which says "use these sqlite structures".

How to copy data between two tables in SQLite?

1 Answers