表的增量更新

Question

我使用SSIS包将数据从Server复制到Oracle，并且希望通过增量更新来提高复制性能。

我的想法是：

1. 追加表的新记录。(在此之前删除它们)
2. 删除“已删除”记录

代码：

 drop table A1
 drop table A2

 --Old Table
 create Table A1(id int,
                 a1 int, a2 int, a3 int, a4 int, a5 int, a6 int, a7 int, a8 int)

 --New Table
 create Table A2(id int,
                 a1 int, a2 int, a3 int, a4 int, a5 int, a6 int, a7 int, a8 int)

 insert into A1 (id, a1, a2, a3, a4, a5, a6, a7, a8) 
 values (1,1,2,3,4,5,6,7,8), (2,2,2,5,4,2,6,2,8), (3,3,2,5,4,5,4,3,8),
        (4,3,1,3,4,5,7,3,8)

 insert into A2 (id, a1, a2, a3, a4, a5, a6, a7, a8) 
 values (1,2,2,3,4,5,6,7,8), (2,2,2,5,4,2,6,2,8), (3,3,2,5,4,5,4,3,8)
--1) Changed values  

 select * from a2
 except
 select * from a1;

 --2)  "Deleted" Values. If I Have primary key in A1,A2 the result is simply get by query
 select a1.* 
 from A1 
 left join A2 on a2.id = a1.id 
 where a2.id is null

但在我的实际情况下，我的桌子上没有主钥匙。我怎么能用另一种方式达到2分，而不按主键连接呢？

查询结果：

id  a1  a2  a3  a4  a5  a6  a7  a8
1   2   2   3   4   5   6   7   8


id  a1  a2  a3  a4  a5  a6  a7  a8
4   3   1   3   4   5   7   3   8

Answer 1

通过使用合并语句，您可以在单个操作中执行插入、更新和删除操作，这对于增量加载非常理想。

样本代码：

    MERGE A2 AS target
    USING (SELECT * FROM A1) AS source 
    ON (target.Id = source.Id AND target.a1 = Source.a1...) -- More join condition
    WHEN MATCHED THEN 
        UPDATE SET a2 = source.a2, a3 = source.a3 ------ more columns
WHEN NOT MATCHED THEN
    INSERT (Id, A1, a1, a2....)
    VALUES (source.Id, source.a1, source.a2.....) 

WHEN NOT MATCHED BY SOURCE
        THEN DELETE 
END;

MERGE语句还提供了捕获插入、删除和更新的记录的灵活性(万一您需要它们来记录日志)，但在您真正需要这些信息之前，我不推荐它们。

在上面的查询中，可以保留要比较的列。

ON (target.Id = source.Id和target.a1 =Source.a1.)-更多的联接

在SSIS中

可以使用查找转换查找匹配记录，但不需要匹配记录，然后插入或删除记录。无论如何，如果您正在考虑将源表与目标表和更新表进行比较时的所有列都是没有意义的。

Answer 2

您可以使用CDC或其他基于日志文件而不是查询的日志传送机制。如果您还想在查询中使用SSIS -

您是否尝试过在Server上使用行版来发现差异，除了之外，它将比更快。

从a2中选择* rowversion > @lastmaxrowversion；

要找到删除是一项艰巨的任务，你能做的最好就是

从不存在的a1中选择a1.id (从a2中选择top 1 1，其中a1.id = a2.id)；

如果您可以添加一个for delete触发器，并将in保存在一个表中，这也是非常好的。

• 请记住，许多公司并不真正关心他们的ETL过程中删除的记录，因为存储不再是一种情况。此外，现在您可以使用分区表存档或活动标志来设计您的系统。无论如何，如果删除仍然很重要，可以尝试为insert/update和delete设置不同的间隔。例如，每隔10分钟检查一次插入/更新，每24小时删除一次。