将数据从一个表更新到另一个表（在数据库中） [英] update data from one table to another (in a database)

问题描述

DB gurus，

我希望有人能设定正确的方向。

I am hoping someone can set set me on the right direction.

有两个表。表A和表B.当系统启动时，来自表A的所有条目被按摩并复制到表B（根据表B的模式）。表A可以有数万行。

I have two tables. Table A and Table B. When the system comes up, all entries from Table A are massaged and copied over to Table B (according to Table B's schema). Table A can have tens of thousands of rows.

系统启动时，表B通过DB更改通知与表A保持同步。

While the system is up, Table B is kept in sync with Table A via DB change notifications.

如果系统重新启动或服务重新启动，我想重新初始化表B.但是，我想这样做与最少可能的数据库更新。具体来说，我想：

If the system is rebooted, or my service restarted, I want to re-initialize Table B. However, I want to do this with the least possible DB updates. Specifically, I want to:

• 添加表A中的所有行，而不是表B中的行，


• 删除不在表A中但在表B中的任何行


• 表A和表B通用的任何行都应保持不变

现在，我不是一个DB人，所以我想知道什么是常规的做法。

Now, I am not a "DB guy", so I am wondering what is conventional way of doing this.

推荐答案

使用存在将处理保持到最低。

Use exists to keep processing to a minimum.

沿着这些线，修改所以连接是正确的（也验证我没有做一些愚蠢，得到 TableA 和 TableB 从您的描述向后）：

Something along these lines, modified so the joins are correct (also verify that I didn't do something stupid and get TableA and TableB backwards from your description):

insert into TableB
    select 
        *
    from
        TableA a
    where
        not exists (select 1 from TableB b where b.ID = a.ID)

delete from 
    TableB b
where
    not exists (select 1 from TableA a where a.ID = b.ID)

这篇关于将数据从一个表更新到另一个表（在数据库中）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！