How to insert large amount of data very quickly while take it as .sql file?

Question

1.00/5 (2 votes)

See more:

SQL-Server-2008R2

Using below command you import the data to sql table, using command prompt we can.

sqlcmd -S server\MYSQLSERVER -d demo -U Test -P test234 -i C:\Users\rja\Desktop\Ra\dbo.WebBP.sql

Its working fine.

If *.sql file size is more than 3 GB. It takes time to insert more than half a day.

But, I need to insert immediately.

Please anyone help me.

What I have tried:

I tried import data in sql server. But that is not working.

Posted 26-Jul-16 2:56am

Member 12637959

Updated 26-Jul-16 4:31am

Add a Solution

Comments

Richard Deeming 26-Jul-16 9:29am

You might be able to improve the performance by changing the query in your .sql file, or by tweaking the indexes on your database.

But I doubt you'll ever be able to load and insert a 3GB file instantly. Just reading that much data into memory and then transferring it over the network to SQL is going to take a noticeable amount of time.

Afzaal Ahmad Zeeshan 26-Jul-16 9:49am

Do what Richard has said, or buy a powerful computer.

Dave Kreskowiak 26-Jul-16 10:26am

What's in the .SQL file? Where did this file come from? Do you have control over its generation?

If it's just a bunch of INSERT statements, you could possibly scrap that and use SSIS to Bulk Insert the data.

There is no way on earth you're ever going to get an "instant" insert of that much data.

1 solution

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)

Patrice T · Answer 1 · 2016-07-26T04:31:00

Assuming the bottle neck is network transfert during base appending.
You can use a 2 steps method.
1) create an 'import' base with right structure on server.
2) copy your data from client to 'import base on server.
3) append the 'import' base to real base (the data is already local)
4) delete 'import' base
This avoid the slow network transfert during appending to database.