什么是在SQL服务器中插入太多（10K +）记录的最佳方法 [英] What is best way to insert too many (10K+) records in SQL server

问题描述

我想上传一个包含20列和超过10K记录的excel文件。这需要花费很长时间（超过5分钟）上传，因为所有数据在插入表之前都在sql中验证。请建议处理这种情况的最佳方法。

I want to upload a excel file having 20 columns and more than 10K records. This is taking more than too long time(more than 5 min) to upload because all data is validated in sql before inserting into table. Kindly suggest the optimum way to handle this scenario.

推荐答案

我会用以下三种方式之一：

首先：

1.将未经验证的数据插入专门为此目的创建的表中 - 只需导入excel表即可轻松完成。

2.验证表中的所有内容，标记或更正有效/无效

3.从表中大量插入经过验证的数据

4.成功插入真实表后，清除临时表（如果是一次性工作）





秒（不确定这会因记录数量而起作用，但它对我有用2000+记录 - 持续三秒钟）：

1.以简单格式创建包含excel数据的XML

I would do it one of three ways:
First:
1. insert unvalidated data into table specifically created for this purpose - this can be easily done by simply importing your excel sheet.
2. validate everything in the table, marking or correcting valid/invalid
3. do a bulk insert of validated data from the table
4. after successful insert into "real" table, clear the temporary one (or drop it if its one-time job)


Second (not sure this would work due to number of records, but it worked for me on 2000+ records - lasted all of three seconds):
1. Create XML containing your excel data in some simple format

<all_data>
<row col1="x" col2="4" col3="7.5" etc...="" />
<row col1="x" col2="4" col3="7.5" etc...="" />
</all_data>

2.传递该XML作为存储过程的参数

3.直接从该XML参数插入，因为您需要先进行验证，也可以在插入之前创建表变量来保存和验证数据

4如果你做了表变量验证，那么你需要从t插入hat table

2. Pass that XML as parameter to the stored procedure
3. Insert directly from that XML parameter, since you need validation first, maybe create table variable to hold and validate the data before insert
4. if you did table variable validation, then you need to insert from that table

有几种方法可以将批量数据从Excel文件插入到数据库中;我们的目标是更快地插入并执行C＃应用程序的插入。以下是技术



1）SQL BULK INSERT查询

2）使用C＃或VB插入批量数据的BCP或SqlBulkCopy库/>
3）SQl服务器集成服务（SSIS）

4）C＃或VB中的普通SQL命令库

There are several ways to insert bulk data from a Excel file to a database; our goal was to perform faster insertion and execute the insertion from a C# application. Following are the techniques

1)SQL BULK INSERT query
2)BCP or SqlBulkCopy library to insert bulk data using C# or VB
3)SQl Server Integration Service (SSIS)
4)Normal SQL command library in C# or VB

这篇关于什么是在SQL服务器中插入太多（10K +）记录的最佳方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！