如何将数据从一个表复制到另一个表(其中两个表都有其他不相同的字段) ？

小开

最佳答案

INSERT INTO Destination SELECT * FROM Source;

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

小开

If you're copying data like that, that probably means your datamodel isn't fully normalized, right? Is it possible to make one list of countries and do a JOIN more?

Instead of a JOIN you could also use virtual tables so you don't have to change the queries in your system.

小开

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;

小开

insert into firstTable (column1, column2, ...) select column1, column2,...  from secondTable;