如何确定在 MERGE 语句期间哪些行/哪些行导致错误以及原因 [英] How to identify which row/rows are causing an error during a MERGE statement and why
问题描述
我在 C# 批处理作业中使用以下代码来处理大量 (20k+) 更新和插入.但是，在测试过程中，我可以看到，如果出现问题，例如违反约束，我只会返回第一条错误消息，而没有关于导致问题的记录(或记录)的信息.
I am using the code below in my C# batch job to handle a large amount (20k+) of updates and inserts. However, during testing I can see that if there is an issue, such as a constraint violation, I will only get the first error message back and no information on which record (or records) it is that caused the problem.
是否有可以使用 .Net 或 T-SQL 实现的错误处理方法或技术，可以为我们提供此功能?
Is there an error handling method or technique that can be achieved either using .Net or T-SQL that could give us this functionality?
C#
private static string insertCommand =
"INSERT (ChannelCode, DrmTerrDesc, IndDistrnId, StateCode, ZipCode, EndDate, EffectiveDate, LastUpdateId, LastUpdateDate, ErrorCodes, Status) " +
"VALUES(Source.ChannelCode, Source.DrmTerrDesc, Source.IndDistrnId, Source.StateCode, Source.ZipCode, Source.EndDate, Source.EffectiveDate, Source.LastUpdateId, Source.LastUpdateDate, Source.ErrorCOdes, Source.Status)";
private static string updateCommand = "UPDATE SET Target.ChannelCode = Source.ChannelCode, Target.DrmTerrDesc = Source.DrmTerrDesc, Target.IndDistrnId = Source.IndDistrnId," +
"Target.StateCode = Source.StateCode, Target.ZipCode = Source.ZipCode, Target.EndDate = Source.EndDate, Target.EffectiveDate = Source.EffectiveDate," +
"Target.LastUpdateId = Source.LastUpdateId, Target.LastUpdateDate = Source.LastUpdateDate, Target.ErrorCodes = Source.ErrorCodes," +
"Target.Status = Source.Status ";
public static int Update(List<ZipCodeTerritory> updates, Dictionary<object, string> errorList)
{
int results = 0;
try
{
//Load updates into datatable
DataTable table = LoadData(updates, true);
//Script to create temp table
string tmpTable = "CREATE TABLE [dbo].[ZipCodeTerritoryTemp]( " +
"[ChannelCode] [char](1) NOT NULL, " +
"[DrmTerrDesc] [nvarchar](30) NOT NULL, " +
"[IndDistrnId] [char](3) NULL, " +
"[StateCode] [char](3) NOT NULL, " +
"[ZipCode] [char](9) NULL, " +
"[EndDate] [date] NOT NULL, " +
"[EffectiveDate] [date] NOT NULL, " +
"[LastUpdateId] [char](8) NULL, " +
"[LastUpdateDate] [date] NULL, " +
"[Id] [int] NULL, " +
"[ErrorCodes] [varchar](255) NULL, " +
"[Status] [char](1) NULL)";
using (SqlConnection connection = new SqlConnection(connString))
{
connection.Open();
//Create temp table
SqlCommand cmd = new SqlCommand(tmpTable, connection);
cmd.ExecuteNonQuery();
try
{
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
{
//Write to temp table
bulkCopy.DestinationTableName = "ZipCodeTerritoryTemp";
bulkCopy.WriteToServer(table);
//Merge changes in temp table with ZipCodeTerritory
string mergeSql = "merge ZipCodeTerritory as Target " +
"using ZipCodeTerritoryTemp as Source " +
"on " +
"Target.Id = Source.Id " +
"when matched then " +
updateCommand +
"when not matched then " +
insertCommand + ";";
cmd.CommandText = mergeSql;
results = cmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
SendEmail.ErrorMail(ex.Message);
}
finally
{
//Drop temp table
SqlCommand final = new SqlCommand("DROP TABLE [dbo].[ZipCodeTerritoryTemp]", connection);
final.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
SendEmail.ErrorMail(ex.Message);
}
return results;
}
推荐答案
简短的回答是，您无法通过执行 MERGE 语句来确定这一点，您必须在执行之前检查这些冲突执行MERGE.
The short answer is that you can't determine this by executing the MERGE statement, you have to check for these collisions prior to executing the MERGE.
换句话说(我再怎么强调也不为过):始终验证您的输入.
In other words (and I can't stress this one enough): always validate your inputs.
您可以在两点进行验证:在将数据批量复制到临时表之前和将临时表合并到目标之前.根据数据问题的性质，您可能能够在数据到达服务器之前进行大量验证.
There are two points where you can do the validation: before you bulk-copy the data to the temporary table and before you merge the temporary table into the target. Depending on the nature of your data issues you might be able to do the bulk of your validation before the data hits the server.
我在使用 MERGE 语句时通常会遇到三类主要问题:
There are three primary classes of problems I've generally encountered with MERGE statements:
- 键冲突(源中的重复行)
- 数据格式错误(例如，日期的字符串表示无法正确转换为
DATE) - 约束失败(不允许出现空值、外键错误等)
通常可以在将数据推送到服务器之前检测到前两个.第三个取决于您的约束的性质……但通常我们可以在它们到达服务器之前解决这些问题.
The first two can generally be detected prior to pushing the data up to the server. The third depends on the nature of your constraints... but generally we can solve these before they get to the server.
可以通过按键(在本例中为 Id)对数据进行分组来检测数据中的键冲突.假设您有两个具有相同 Id 值的记录，但您希望合并具有最高 LastUpdateDate 的记录.一种选择是:
Key collisions in your data can be detected by grouping the data by the keys (Id in this case). Let's assume that you have two records with the same Id value, but you want to merge in the one with the highest LastUpdateDate. One option for this is:
var cleanupdates =
from update in updates
group update by update.Id into grp
select grp.OrderByDescending(u => u.LastUpdateDate).First();
如果您的约束问题与空值有关，请使用 where 子句过滤掉那些具有无效空值的记录.如果它们与外键约束相关，请将这些键加载到列表中并对其进行过滤.您可以使用 LINQ 查询进行大量验证.
If your constraint problems are related to null values, filter out those records that have invalid nulls using a where clause. If they're related to foreign key constraints, load those keys into a list and filter on them. You can cover a lot of the validation using LINQ queries.
重要的一点是你做验证.否则您的 MERGE 将失败，您将不知道为什么.
The important point is that you do the validation. Otherwise your MERGE will fail, and you won't know why.
这篇关于如何确定在 MERGE 语句期间哪些行/哪些行导致错误以及原因的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
