执行通过C＃的SqlCommand MERGE语句不工作 [英] Executing merge statement via C# SqlCommand not working
问题描述
我第一次尝试使用临时表和 MERGE 语句通过 SqlCommand object in C＃。我正在开发的程序设计首先导出一个非常大的记录（超过20k +）到一个excel电子表格。然后，用户具有对特定值进行搜索和替换的能力，并且在尽可能多的记录中更新许多字段。
我想要做的是接着使用该电子表格，用它填充 DataTable ，然后填充使用 SqlBulkCopy 的临时SQL表与 DataTable 。
然后，使用 MERGE 语句更新行，如果它们仍然存在于数据库中。
但是，我所遇到的问题是我在 ZipCodeTerritory 表上持有的唯一约束我出现以下错误消息：
无法使用唯一索引UQ_ChannelStateEndDateZipCodeISNULL在对象dbo.ZipCodeTerritory中插入重复键行。重复键值为（9，CA，94351，9999-12-31）。
这让我相信， UPDATE 语句没有被执行，或者我不知何故在语句的一部分使用 ON 关键字。唯一约束仅在 INSERT 语句或 UPDATE 到 ChannelCode ， StateCode ， ZipCode 或 EndDate 中。我正在对 IndDistrnId 字段进行批量更新，并彻底检查了电子表格。
再次，这是我第一次尝试这种技术，所以任何帮助/建议将非常感谢。感谢
C＃
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;
//将更新加载到数据表中
DataTable table = LoadData（updates）;
//创建临时表的脚本
string tmpTable =CREATE TABLE [dbo]。[ZipCodeTerritoryTemp]（+
[ChannelCode] [char] NULL，+
[DrmTerrDesc] [nvarchar]（30）NOT NULL，+
[IndDistrnId] [char] char]（3）NOT NULL，+
[ZipCode] [char]（9）NULL，+
[EndDate] [date] NOT NULL，+
EffectiveDate] [date] NOT NULL，+
[LastUpdateId] [char]（8）NULL，+
[LastUpdateDate] [date] NULL，+
] [int] IDENTITY（1,1）NOT NULL，+
[ErrorCodes] [varchar]（255）NULL，+
+
CONSTRAINT [PK_ZipCodeTerritoryTemp] PRIMARY KEY NONCLUSTERED+
（+
[Id] ASC+
）WITH（PAD_INDEX = OFF，STATISTICS_NORECOMPUTE = IGNORE_DUP_KEY = OFF，ALLOW_ROW_LOCKS = ON，ALLOW_PAGE_LOCKS = ON，FILLFACTOR = 90）ON [PRIMARY]+
）ON [PRIMARY];
使用（SqlConnection connection = new SqlConnection（connString））
{
connection.Open（）;
//创建临时表
SqlCommand cmd = new SqlCommand（tmpTable，connection）;
cmd.ExecuteNonQuery（）;
try
{
使用（SqlBulkCopy bulkCopy = new SqlBulkCopy（connection））
{
//写入临时表
bulkCopy.DestinationTableName =ZipCodeTerritoryTemp;
bulkCopy.WriteToServer（table）;
//使用ZipCodeTerritoryTemp作为源+
在+
上合并使用ZipCodeTerritory更改临时表
string mergeSql =合并ZipCodeTerritory作为目标+
Target.Id = Source.Id+
匹配时，+
updateCommand +;;
cmd.CommandText = mergeSql;
int results = cmd.ExecuteNonQuery（）;
//删除临时表
cmd.CommandText =DROP TABLE [dbo]。[ZipCodeTerritoryTemp];
cmd.ExecuteNonQuery（）;
}
}
catch（Exception）
{
throw;
}
finally
{
//删除临时表
SqlCommand final = new SqlCommand（DROP TABLE [dbo]。[ZipCodeTerritoryTemp]，connection）;
final.ExecuteNonQuery（）;
}
}
SQL >
为了方便阅读，我在SQL Server Management Studio中写了 MERGE 语句。我把这个拷贝到C＃中。 FYI - 在Management Studio中运行此语句，并收到完全相同的错误消息。
MERGE INTO ZipCodeTerritory as target
使用ZipCodeTerritoryTemp作为来源
ON Target.Id = Source.Id
WHEN MATCHED THEN
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;
> IDENTITY 属性设置在临时表中的 Id 字段。删除后，我可以运行 MERGE 没有错误。现在是临时表：
//创建临时表的脚本
string tmpTable =CREATE TABLE [dbo] 。[ZipCodeTerritoryTemp]（+
[ChannelCode] [char]（1）NOT NULL，+
[DrmTerrDesc] [nvarchar] [ZipCode] [char]（j）NULL，+
[ZipCode] [char] [IndDistrnId] [char]
[EndDate] [date] NOT NULL，+
[EffectiveDate] [date] NOT NULL，+
[LastUpdateId] [char]
[LastUpdateDate] [date] NULL，+
[Id] [int] NOT NULL，+ //不要提供温度表的PK PROPRETY
[ErrorCodes] [varchar]（255）NULL，+
[Status] [char]（1）NULL，+
CONSTRAINT [PK_ZipCodeTerritoryTemp] PRIMARY KEY NONCLUSTERED （PAD_INDEX = OFF，STATISTICS_NORECOMPUTE = OFF，IGNORE_DUP_KEY = OFF，ALLOW_ROW_LOCKS = ON，ALLOW_PAGE_LOCKS = ON，FILLFACTOR = ON， 90）ON [PRIMARY]+
）ON [PRIMARY];
I am making my first attempt at using a temp table and a MERGE statement to update a SQL table via a SqlCommand object in C#. The program I'm working on is designed to first export a very large set of records (upwards of 20k+) into an excel spreadsheet. The user then has the ability to do a search and replace for a specific value and update as many fields in as many records as they like.
What I'm trying to do is then take that spreadsheet, populate a DataTable with it, and then populate a temporary SQL table with the DataTable using SqlBulkCopy.
I then use a MERGE statement to update the rows if they still exist in the database.
However, the problem I'm having is a Unique Constraint I have on the ZipCodeTerritory table keeps being triggered giving me the following error message:
Cannot insert duplicate key row in object 'dbo.ZipCodeTerritory' with unique index 'UQ_ChannelStateEndDateZipCodeISNULL'. The duplicate key value is (9, CA , 94351 , 9999-12-31).
This leads me to believe that somehow either the UPDATE statement isn't being executed or I have somehow joined the tables incorrectly in the part of the statement using the ON keyword. The unique constraint is only triggered during INSERT statements or an UPDATE to the ChannelCode, StateCode, ZipCode or EndDate fields. I am doing a mass update to the IndDistrnId field and have thoroughly checked the spreadsheet.
Again, this is my first attempt at trying this technique so any help/suggestions would be greatly appreciated. Thanks
C#
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 ";
//Load updates into datatable
DataTable table = LoadData(updates);
//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] IDENTITY(1,1) NOT NULL, " +
"[ErrorCodes] [varchar](255) NULL, " +
"[Status] [char](1) NULL, " +
"CONSTRAINT [PK_ZipCodeTerritoryTemp] PRIMARY KEY NONCLUSTERED " +
"( " +
"[Id] ASC " +
")WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY] " +
") ON [PRIMARY]";
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 + ";";
cmd.CommandText = mergeSql;
int results = cmd.ExecuteNonQuery();
//Drop temp table
cmd.CommandText = "DROP TABLE [dbo].[ZipCodeTerritoryTemp]";
cmd.ExecuteNonQuery();
}
}
catch (Exception)
{
throw;
}
finally
{
//Drop temp table
SqlCommand final = new SqlCommand("DROP TABLE [dbo].[ZipCodeTerritoryTemp]", connection);
final.ExecuteNonQuery();
}
}
SQL
For readability's sake here's the MERGE statement as I wrote it in SQL Server Management Studio. I copied this into the C#. FYI - ran this statement in Management Studio and received the exact same error message.
MERGE INTO ZipCodeTerritory as Target
USING ZipCodeTerritoryTemp as Source
ON Target.Id = Source.Id
WHEN MATCHED THEN
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;
The issue wound up being the IDENTITY property being set on the Id field in the temp table. After removing this I was able to run the MERGE without error. Here's the temp table now:
//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] NOT NULL, " + //DO NOT GIVE THE PK OF THE TEMP TABLE AN IDENTITY(1,1,) PROPRETY
"[ErrorCodes] [varchar](255) NULL, " +
"[Status] [char](1) NULL, " +
"CONSTRAINT [PK_ZipCodeTerritoryTemp] PRIMARY KEY NONCLUSTERED " +
"( " +
"[Id] ASC " +
")WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY] " +
") ON [PRIMARY]";
这篇关于执行通过C＃的SqlCommand MERGE语句不工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
