What is best way to insert too many (10K+) records in SQL server

Question

1.00/5 (1 vote)

See more:

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.

Posted 8-Oct-14 20:23pm

Member 10815407

Add a Solution

Comments

Sinisa Hajnal 9-Oct-14 2:30am

What is your current way of doing it? Do you validate in SQL then return control to the app to insert?

Member 10815407 9-Oct-14 2:41am

Yes, After validating data in sql it is inserted in DB table then return control to application to display the status of insertion(error in validation and how many records inserted out of total records)

2 solutions

Solution 3

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

Posted 9-Oct-14 19:38pm

Santhosh Babu Mahimairaj

Comments

Member 10815407 17-Oct-14 1:59am

But I need to perform multiple validation over the data

[no name] 17-Oct-14 2:36am

you can do n number of valaidations in above techniques

Member 10815407 17-Oct-14 5:32am

Suppose I choose SQL Bulk insert option and insert data into temp table. After validating data in temp table, I am inserting all data to main table. But this process taking more than 3 mins when total number of records>5K
So user can't wait for 3 min without any status(Data is uploading or not). Here we can display a progress bar but How? I have no idea

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sinisa Hajnal · Accepted Answer · 2014-10-08T20:37:00

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

XML

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

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