C# 多行插入 -> 语句超过了 1000 个行值的最大允许数量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/821185/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Multiple row insert -> statement exceeds the maximum allowed number of 1000 row values
提问by Gerbrand
I'm using sql 2008 express edition and I'm trying to do a multiple row insert thru my C# application.
我正在使用 sql 2008 快速版，我正在尝试通过我的 C# 应用程序执行多行插入。
I've got around 100000 records that needs to be inserted.
我有大约 100000 条记录需要插入。
Okay all goes well for the first 1000 records then I'm getting the error:
好的，前 1000 条记录一切顺利，然后我收到错误消息：
"The number of row value expressions in the INSERT statement exceeds the maximum allowed number of 1000 row values."
“INSERT 语句中的行值表达式的数量超过了 1000 个行值的最大允许数量。”
I looked at my column data type -> int, so that shouldn't be the problem. I checked my code and I'm inserting in steps of 500 records.
我查看了我的列数据类型 -> int，所以这应该不是问题。我检查了我的代码，并以 500 条记录的步骤插入。
So I googled it but couldn't find anything useful. Can somebody explain why I get this error and if possible how to solve it.
所以我用谷歌搜索但找不到任何有用的东西。有人可以解释为什么我收到这个错误，如果可能的话如何解决它。
采纳答案by Jeremy
You can use the SQLBulkCopyclass. Which supports batching, transactions and is more efficient than standard insert statements.
您可以使用SQLBulkCopy类。它支持批处理、事务并且比标准插入语句更有效。
回答by Gerbrand
this is how my code handles the multi insert
这就是我的代码处理多插入的方式
var count = "SELECT COUNT(*) as rowcount FROM table_mysql GROUP BY id";
var countReader = Retrieve(count);
var countRows = 0;
try
{
while (countReader.Read())
{
countRows += int.Parse(countReader.GetValue(0).ToString());
}
}
catch (Exception ex)
{
Log.LogMessageToFile("Import.cs -> table_mssql: " + ex.StackTrace + " /n" + ex.Message);
}
finally
{
if (countReader != null) { countReader.Close(); _crud.close_conn(); }
}
for (var a = 0; a < countRows; )
{
var sql = "SELECT id, traffic_id, dow, uu, imps, impsuu, otsw, otsm FROM table_mysql LIMIT " + a + ", " + (a + 500) + "";
var reader = Retrieve(sql);
try
{
var builder = new StringBuilder();
builder.Append(
"SET IDENTITY_INSERT table_mssql ON;INSERT INTO table_mssql(id, traffic_id, dow, uu, imps, impsuu, otsw, otsm) VALUES ");
while (reader.Read())
{
Application.DoEvents();
try
{
builder.Append("(" + reader.GetValue(0) + ", " + reader.GetValue(1) + ", " +
reader.GetValue(2) +
", " + reader.GetValue(3) + ", " + reader.GetValue(4) +
", " + reader.GetValue(5) + ", " + reader.GetValue(6) + ", " +
reader.GetValue(7) +
"), ");
}
catch (Exception ex)
{
Log.LogMessageToFile("Import.cs -> table_mssql: " + ex.StackTrace + " /n" + ex.Message);
}
}
var sqlDB = builder.ToString(0, builder.Length - 2);
sqlDB += ";SET IDENTITY_INSERT table_mssql OFF;";
if (!InsertDB(sqlDB))
{
Log.LogMessageToFile("Import.cs -> table_mssql: No insert happend!");
}
}
catch (Exception ex)
{
Log.LogMessageToFile("Import.cs -> table_mssql: " + ex.StackTrace + " /n" + ex.Message);
return false;
}
finally
{
if (reader != null)
{
reader.Close();
_crud.close_conn();
}
}
a = a + 500;
}
I'm going to check the sqlbulkcopy. Maybe that is a better solution.
我要检查 sqlbulkcopy。也许这是一个更好的解决方案。
回答by Joel Coehoorn
You can take that whole thing down to this:
你可以把整个事情归结为：
var sql = "SET IDENTITY_INSERT table_mssql ON;"
+ "INSERT INTO table_mssql"
+ "(id, traffic_id, dow, uu, imps, impsuu, otsw, otsm)"
+ " SELECT id, traffic_id, dow, uu, imps, impsuu, otsw, otsm "
+ " FROM table_mysql;"
+ "SET IDENTITY_INSERT table_mssql OFF;";
if (!InsertDB(sqlDB))
{
Log.LogMessageToFile("Import.cs -> table_mssql: No insert happend!");
}
Also: you should know that Sql Server doesn't support MySql's LIMITkeyword. It uses TOPor ROW_NUMBERinstead.
另外：您应该知道 Sql Server 不支持 MySql 的LIMIT关键字。它使用TOP或ROW_NUMBER代替。
