Excel 到 SQL。批量复制的替代方案？
我有一段代码，它使用 DataReader 和 SqlBulkCopy 将数据从 Excel 电子表格复制到 MSSQL 表。它工作得很好，直到我在表上创建了主键，现在它失败了。我首先删除 SQL 表的内容，然后再次用 Excel 中的数据填充它。
由于我只移动少量数据，我想知道是否有比使用 BulkCopy 更好的方法来做到这一点？
更新：下面是相关代码，我收到的错误是： “数据源中 String 类型的给定值无法转换为指定目标列的 float 类型。”
using (OleDbConnection connection = new OleDbConnection(excelConnectionString))
{
connection.Open();
OleDbCommand cmd = new OleDbCommand
("SELECT Name, Date, Amount FROM ExcelNamedRange", connection);
using (OleDbDataReader dr = cmd.ExecuteReader())
{
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
{
bulkCopy.DestinationTableName = "SqlTable";
bulkCopy.WriteToServer(dr);
}
}
}
I have a piece of code which copies data from an excel spreadsheet to a MSSQL table using DataReader and SqlBulkCopy. It worked fine until I created a primary key on the table and now it fails. I am first deleting the contents of the SQL table before filling it again with the data from excel.
As it is only a small amount of data I am moving, I wondered if there was a better way to do this than using BulkCopy?
Update: below is the relative code and the error I receive is:
"The given value of type String from the data source cannot be converted to type float of the specified target column."
using (OleDbConnection connection = new OleDbConnection(excelConnectionString))
{
connection.Open();
OleDbCommand cmd = new OleDbCommand
("SELECT Name, Date, Amount FROM ExcelNamedRange", connection);
using (OleDbDataReader dr = cmd.ExecuteReader())
{
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
{
bulkCopy.DestinationTableName = "SqlTable";
bulkCopy.WriteToServer(dr);
}
}
}
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
SqlBulkCopy 自动映射字段。但由于您添加了主键，默认映射不再有效。
您必须设置 ColumnMapping 来明确告诉 SqlBulkCopy 对象如何映射字段。
对除主键之外的所有字段执行此操作（假设您在 PK 上使用身份）。
例如：
SqlBulkCopy automatically maps the fields. But since you added a primary key that default mapping is no longer valid.
You will have to set ColumnMapping to tell your SqlBulkCopy object explicityly how to map the fields.
Do this for all your fields, except the primary key (assuming you use an identity on the PK).
For example:
创建主键表明您正在强制执行域约束（这是一件好事）。
因此，您的实际问题不是您需要另一种方法来执行批量插入，而是您需要找出为什么有重复的键（强制执行的确切原因） PK）。
Creating a primary key, suggests you are enforcing a domain constraint (a good thing).
Therefore, your actual problem is not that you need another way to perform the bulk insert, but that you need to find out why you have duplicate keys (the precise reason for enforcing the PK).
BulkCopy 应该可以正常工作，因此您的问题似乎是重复的键（错误消息是什么？）。您要么有错误的数据，要么您创建的主键太窄。
您还可以做的就是首先将数据推送到临时表中（没有键/索引等，只是一个普通表），然后使用更新（2008 年时合并）语句将其放入实际表中。
国杰
BulkCopy should work just fine, so your problem seems to be a duplicate key (what's the error message?). You either have data that that is wrong there, or the primary key you created is too narrow.
What you could also do is push the data into a staging table first (no keys/ indexes etc, just a plain table) and then use an update (merge when on 2008) statement to put it into the actual table.
GJ
好吧，这似乎完全是一个不同的问题，年份 ExcelNamedRange 中似乎有一个值无法转换为 SqlTable 中的列之一。你能看到吗？也许除以 0 错误等？
还要确保列对齐。不确定 SqlBulkCopy 到底如何映射列，我认为它只是将 NamedRange 中的第一列放入 SqlTale 等的第一列中。所以请确保它们的顺序正确。 （或者看看如果你更改名称会发生什么）
ok that seems to be a different problem altogether, there seems to be a value in year ExcelNamedRange that cannot be cast as one of the columns in SqlTable. Can you see any? Maybe division by 0 error etc?
Also make sure the columns line up. Not sure exactly how SqlBulkCopy maps the columns, I think it just puts the first column from NamedRange into the first column of SqlTale etc. So make sure they;re in the right order. (or see what happens if you change the names)
BulkCopy 是最快的方法，如何从 C# 将数据插入 MSSQL。
The BulkCopy is fastest way, how to insert data into MSSQL from C#.