SSIS比较表内容并更新另一个 [英] SSIS Compare tables content and update another

问题描述

我想比较两个表，一个来自Access Mdb&一个SQL Server表是SSIS.

I would like to compare two tables, one from Access Mdb & one SQL server table is SSIS.

目标是在存在差异的情况下截断该表，并用MDB源填充它并在另一个表中更新版本.

The goal is to truncate the table if there is differences and fill it with MDB source and Update the version in another table.

我正在尝试通过查找来实现，但是Version随着行数的增加而增加，应该为一次.

I am trying to do it through a lookup but the Version is incremented with the number of rows and it should be once.

该操作需要使用2个不同的表来执行.

The operation needs to be performed with 2 different Tables.

推荐答案

即使我有10个否，我应该如何只执行一次更新 匹配输出行?

How am I supposed to perform only once the update even if I have 10 No Match output rows ?

SSIS提供了OLEDB命令，用于在管道中执行数据流中的更新.但是，正如您所暗示的那样，此操作每行执行一次UPDATE命令.

SSIS provides the OLEDB Command for performing in the pipeline updates in a data flow. However, as you have implied in your question, this performs an UPDATE command per row.

执行批处理级别更新的一种更有效的技术是:

A more efficient technique for performing batch level updates is to:

1. 截断UPDATE登台表
2. 检测数据流中哪些行已更改
3. 将已更改的行定向到目标并将数据存储在登台表中

4. 在数据流之后添加执行SQL命令，该命令将执行从登台表到目标表的更新.即

1. Truncate UPDATE staging table
2. Detect what rows have changed in the data flow
3. Direct changed rows to a destination and store the data in a staging table

4. Add an execute SQL command after the data flow which will perform an update from the staging table to the target table. i.e.

UPDATE T
SET T.Column1 = S.Column1
    , T.Column2 = S.Column2
FROM MyTarget T 
    JOIN MySource S ON T.id = S.id

控制流如下所示:

编辑了步骤和图像，以添加用于删减登台表的步骤.

Edited Steps and image to add a step for truncating the staging table.

这篇关于SSIS比较表内容并更新另一个的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！