合并声明SSIS [英] Merge Statement SSIS
问题描述
我在SSMS上测试了此代码
使用
合并dim_BTS AS Target(
SELECTA。 BTS,D.idVille
来自广播A
内部联接dbo.DIM_AXE_GEO D
ON A.Ville = D.Villle
)AS Source ON Source.BTS =匹配后的Target.BTS
更新
SET Target.idVille = Source.idVille;
向我显示此错误
MERGE语句尝试多次更新或删除同一行
。当目标行与多个源
行匹配时,就会发生这种情况。 MERGE语句不能多次更新/删除
目标表的同一行。优化ON子句以确保目标
行最多匹配一个源行,或使用GROUP BY子句将
分组到源行。
能否请你帮我该怎么办?
您的源子查询是返回具有相同 BTS
(您用来连接目标的列)的重复行,这<< c $ c> MERGE 语句是不允许的。 / p>
您可以使用 ROW_NUMBER()优化查询以仅过滤每个
在CTE中的功能 BTS
的最新行。 )
与CTE_Source AS
(
SELECT A. BTS,D.idVille,ROW_NUMBER()超过(按A.BTS排序或按d.idVille DESC排序)RN-选择您偏好的顺序
从播出A
内部联接dbo.DIM_AXE_GEO D
ON A.Ville = D.Villle
)
使用
合并dim_BTS AS目标(
SELECT * FROM CTE_Source WRN RN = 1
)AS Source ON Source防弹少年团=匹配后的Target.BTS
然后
更新
SET Target.idVille = Source.idVille;
或者如果需要插入多行BTS,则在加入时需要在ON子句上添加更多列达到目标。
I tested this code on SSMS
Merge dim_BTS AS Target using
(
SELECT A.BTS, D.idVille
FROM onAir A
INNER JOIN dbo.DIM_AXE_GEO D
ON A.Ville = D.Villle
) AS Source ON Source.BTS = Target.BTS
WHEN MATCHED THEN
UPDATE
SET Target.idVille = Source.idVille;
show me this error
The MERGE statement attempted to UPDATE or DELETE the same row more
than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.
Can you please help me what can I do ?
Your Source sub-query is returning duplicate rows with same BTS
(column You use to join on target) which is not allowed for MERGE
statement.
You can refine your query to filter only the latest row for each BTS
using ROW_NUMBER()
function in CTE
WITH CTE_Source AS
(
SELECT A.BTS, D.idVille, ROW_NUMBER() OVER (PARTITION BY A.BTS ORDER BY d.idVille DESC) RN -- choose order of your preference
FROM onAir A
INNER JOIN dbo.DIM_AXE_GEO D
ON A.Ville = D.Villle
)
Merge dim_BTS AS Target using
(
SELECT * FROM CTE_Source WHERE RN=1
) AS Source ON Source.BTS = Target.BTS
WHEN MATCHED THEN
UPDATE
SET Target.idVille = Source.idVille;
Or if multiple row BTS needs to be inserted, you need to add more columns on ON clause when joining on target.
这篇关于合并声明SSIS的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!