删除现有数据并加载新数据 [英] Delete existing data and load new
问题描述
我有一个表dbo.sales,看起来像这样:
dbo.sales:
年  月    sales_order的    金额
2019   001        ABX                400
2019   002        ABY                100
我每天早上都会收到一张快照,所以今天我会加载一张如下表:
年份  月    sales_order的    金额
2019   004        AAA                400
2019   004        BBB                500
但是明天我会收到一张类似的表格,只有更多的行用于期间"004"。我在这个表中没有主键。
我今天在SSIS中所做的是提取年+月("2019","004")的独特组合。在上述情况下)从输入流中删除相应的值 dbo.sales然后再次加载整个月
。
通过找到合适的数据解决方案我遇到了困难厂。我想我将使用存储过程,但是我是否还需要为dbo.sales创建一个包含所有列的类型表,或者只创建我要删除的列?
Hi Morten_DK ,
这样做的最佳方法是使用存储在SQL接收器中的存储过程。还有一个关于如何进行upsert操作的例子。请查看
此示例获取见解。
希望这会有所帮助。
I am having a table dbo.sales which looks something like this:
dbo.sales:
Year Month Sales_order Amount
2019 001 ABX 400
2019 002 ABY 100
I get a monthly snapshot each morning, so today I will load a table looking like this:
Year Month Sales_order Amount
2019 004 AAA 400
2019 004 BBB 500
But tomorrow I will receive a table similar, just with more rows for period "004". I don't have a primary key in this table.
What I am doing today in SSIS is to extract the distinct combination of year + month ("2019", "004" in the above case) from the input stream and then delete the corrosponding values in dbo.sales and then load the entire month again.
I am having difficulties by finding a suitable solution in data factory. I suppose I shall use a stored procedure, but do I also need to create a type table with all my columns for dbo.sales or only the ones I wich to delete?
Hi Morten_DK,
An optimal way to do so is by using a Stored Procedure stored in the SQL sink. There's also an example given on how to do an upsert operation. Please have a look at this example for insights.
Hope this helps.
这篇关于删除现有数据并加载新数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!