SSIS:在更新或插入后删除行 [英] SSIS : delete rows after an update or insert
问题描述
以下是以下情况: 我有一个 StudentsA 表,该表需要与另一台服务器 StudentsB 上的另一个表同步.这是从A到B的单向同步. 由于学生表A可以容纳大量行,因此我们有一个名为 StudentsSync 的表(在输入服务器上),其中包含ID为 StudentsA 的表,自上次修改以来从学生A 复制到学生B .
Here is the following situation: I have a table of StudentsA which needs to be synchronized with another table, on a different server, StudentsB. It's a one-way sync from A to B. Since the table StudentsA can hold a large number of rows, we have a table called StudentsSync (on the input server) containing the ID of StudentsA which have been modified since the last copy from StudentsA to StudentsB.
我完成了以下SSIS数据流任务:
I made the following SSIS Data Flow task:
唯一的问题是,在成功复制或更新后,我需要从 StudentsSync 中删除该行.像这样:
The only problem is that I need to DELETE the row from StudentsSync after a successful copy or update. Something like this:
有什么想法可以实现吗?
Any idea how this can be achieved?
推荐答案
可以使用3种方法实现
1.如果OutputDB
中的target
表具有TimeStamp
列,例如Create
和modified TimeStamp
,则可以通过编写简单查询来获取具有updated
或inserted
的行.您需要在Control Flow
中的execte sql task
中将以下查询写入delete
表中的那些行.
1.If your target
table in OutputDB
has TimeStamp
columns such as Create
and modified TimeStamp
then rows which have got updated
or inserted
can be obtained by writing a simple query. You need to write the below query in the execte sql task
in Control Flow
to delete
those rows in Sync
Table .
Delete from SyncTable
where keyColumn in (Select primary_key from target
where ModifiedTimeStamp >= GETDATE() or (ModifiedTimeStamp is null
and CreateTimeStamp>=GETDATE()))
我假定StudentsA's
primary key
与Target
表的primary key
一起存在于Sync
表中.上面的条件基本上检查,如果new row
是added
,则CreateTimeStamp
列将具有current
日期,而modifiedTimeStamp
将是null
,否则,如果值是updated
,则modifiedTimeStamp
将具有当前日期
I assume StudentsA's
primary key
is present in Sync
table along with primary key
of Target
table. The above condition basically checks, if a new row
is added
then CreateTimeStamp
column will have current
date and modifiedTimeStamp
will be null
else if the values are updated
then the modifiedTimeStamp
will have current date
如果您在target
表中有TimeStamp
列,则上述查询将起作用,如果您将数据加载到Data Warehouse
The above query will work if you have TimeStamp
columns in your target
table which i feel should be there if your loading data into Data Warehouse
2.您可以使用MERGE
语法执行更新并使用Execute SQL Task
插入Control Flow
中.无需使用Data Flow Task
.即使您没有任何TimeStamp columns
2.You can use MERGE
syntax to perform the update and insert in Control Flow
with Execute SQL Task
.No need to use Data Flow Task
.The below query can be used even if you don't have any TimeStamp columns
DECLARE @Output TABLE ( ActionType VARCHAR(20), SourcePrimaryKey INT)
MERGE StudentsB AS TARGET
USING StudentsA AS SOURCE
ON (TARGET.CommonColumn = SOURCE.CommonColumn)
WHEN MATCHED
THEN
UPDATE SET TARGET.column = SOURCE.Column,TARGET.ModifiedTimeStamp=GETDATE()
WHEN NOT MATCHED BY TARGET THEN
INSERT (col1,col2,Col3)
VALUES (SOURCE.col1, SOURCE.col2, SOURCE.Col3)
OUTPUT $action,
INSERTED.PrimaryKey AS SourcePrimaryKey INTO @Output
Delete from SyncTable
where PrimaryKey in (Select SourcePrimaryKey from @Output
where ActionType ='INSERT' or ActionType='UPDATE')
该代码没有经过测试,因为我已经没时间了.但是至少,它应该可以使您对如何进行操作有一个清晰的了解.有关MERGE
语法的更多详细信息,请阅读此
The code is not tested as i'm running out of time .but at-least it should give you a fair idea how to proceed . .For furthur detail on MERGE
syntax read this and this
3.使用Multicast
组件将duplicate
和Update
的数据集duplicate
连接到lookmatch
输出到lookmatch
并将另一个多播连接到Lookup No match output
3.Use Multicast
Component to duplicate
the dataset for Insert
and Update
.Connect a MULTICAST
to lookmatch
output and another multicast to Lookup No match output
这篇关于SSIS:在更新或插入后删除行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!