Jul 6, 2016

Efficient | fast way to populate large volume of data in a table c# SQL Server

Sometimes there is a need to populate | insert a large volume of data from application code(c#, Java, vb.net) to a table in the database (SQL Server).  If data volume is less then performance is not a big concern. But if the data volume is big we must have to consider the most efficient approach. Let us discuss different approaches and their advantages and disadvantages:

Looping over insert statement:
One of the easiest ways to write an insert statement inside a loop. And it will populate records one by one in the database. For example:
foreach (DataRow row in table.Rows) {

    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        SqlCommand cmd = new SqlCommand("INSERT INTO Users(Name, Phone, Age) VALUES (@Name, @Phone, @Age)");
        cmd.CommandType = CommandType.Text;
        cmd.Connection = connection;
        cmd.Parameters.AddWithValue("@Name", row[1]);
        cmd.Parameters.AddWithValue("@Phone", row[2]);
        cmd.Parameters.AddWithValue("@Age", row[3]);
        connection.Open();
        cmd.ExecuteNonQuery();
    }
}
      
Here the problem with above script is there is separate database call for each row.  For a large volume of records above script will take a significant amount of time.

Multiple insert statement in a single query:

In this approach first all insert queries are saved in a string variable. Then whole insert statement is executed in the database at the same time. For example:

string Query = @"INSERT INTO Users(Name, Phone, Age) VALUES ('Name_1', 'Phone_1', 'Age_1')
INSERT INTO Users(Name, Phone, Age) VALUES ('Name_2', 'Phone_2', 'Age_2')
.....
.....
INSERT INTO Users(Name, Phone, Age) VALUES ('Name_n', 'Phone_n', 'Age_n')"

using (SqlConnection connection = new SqlConnection(connectionString))
{
    SqlCommand cmd = new SqlCommand(Query);
    cmd.CommandType = CommandType.Text;
    cmd.Connection = connection;
    connection.Open();
    cmd.ExecuteNonQuery();
}

In the above approach database is called only one time but inside database rows are being inserted one by one. So it is rows based approach and it is not efficient in case of a large volume of data.

Table value constructor:

In SQL server table value constructor is set based approach. And it is very efficient.  Instead of the full insert statement, we can save the query string in table value constructor format. For example:
string Query = @"INSERT INTO Users(Name, Phone, Age)
       VALUES ('Name_1', 'Phone_1', 'Age_1')
       VALUES ('Name_2', 'Phone_2', 'Age_2')
       .....
       .....
       VALUES ('Name_n', 'Phone_n', 'Age_n')"
                                 

Although this approach is very efficient but limited to 1000 rows. If there are more than 1000 rows above approach is not going to work.

Passing Data table:
One of the best approaches is passing the data table as a parameter to stored procedure. For example:

Create a table type in SQL server:
CREATE TYPE UserType AS TABLE(
      Name VARCHAR(50) NOT NULL,
      Phone VARCHAR(10),
      Age INT
)

Create a stored procedure which accepts UserType as a parameter:

CREATE PROCEDURE AddUsers(
    @UserTable UserType READONLY
)
AS
BEGIN
    INSERT INTO Users
      SELECT * FROM @UserTable
END

Using C# code, execute stored procedure and pass data table as a parameter:

using (SqlConnection conn = new SqlConnection(conn))
using (SqlCommand cmd = new SqlCommand("dbo.AddUsers", conn))
{
    var dt = new DataTable();

    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add(new SqlParameter("@UserType", dt));  
    conn.Open();
    cmd.ExecuteNonQuery();
         
}

Using bulk insert:

SQL server support bulk inserts that loading the data from a file. For this first, we have to download or copy data file at the specified location using programming language. Then using BULK INSERT command data can be imported into a table. For example:

BULK INSERT EXACT_HELP.dbo.Users
FROM 'C:\Data\Users.txt' 
WITH
      ( 
            FIELDTERMINATOR =' |', 
            ROWTERMINATOR =' |\n' 
      ); 

We can also do by using C# SqlBulkCopy.

Pass data in the XML format in SQL server:

In your programming language, first of all, covert whole data table in XML format. Pass that XML to a stored procedure to populate the table. For example:

Create a stored procedure:

CREATE PROCEDURE AddUers(
    @UserTable XML
)
AS
BEGIN

      DECLARE @idoc int

      EXEC sp_xml_preparedocument @idoc OUTPUT, @UserTable;

    INSERT INTO Users
      SELECT  * 
      FROM OPENXML (@idoc, '/ROOT/Users',1) 
    WITH (
            Name VARCHAR(50),
            Phone VARCHAR(10),
            Age INT)

END

C# code to pass XML data:
                  
DataSet ds = new DataSet();
ds.Tables.Add(dt1);
string UserXml= ds.GetXml();

using (SqlConnection conn = new SqlConnection(conn))
using (SqlCommand cmd = new SqlCommand("dbo.AddUsers", conn))
{
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add("@UserTable", SqlDbType.Xml);   
    cmd.Parameters["@UserTable"].Value = UserXml;        
    conn.Open();
    cmd.ExecuteNonQuery();
         
}

262 comments:

«Oldest   ‹Older   201 – 262 of 262
rstrainings said...


I am really happy to say it’s an interesting post to read . I learn new information from your article , you are doing a great job . Keep it up

Devops Training in Hyderabad

Hadoop Training in Hyderabad

Python Training in Hyderabad

Tableau Training in Hyderabad

Selenium Training in Hyderabad

Professional Course said...

A good blog always contains new and exciting information, and reading it I feel like this blog really has all of these qualities that make it a blog.

AI Training in Bangalore

princika said...

I want to leave a little comment to support and wish you the best of luck.we wish you the best of luck in all your blogging enedevors
iot training in delhi

Unknown said...

Excellent Blog! I would like to thank for the efforts you have made in writing this post. I am hoping the same best work from you in the future as well. I wanted to thank you for this websites! Thanks for sharing. Great websites!.iot course in delhi

traininginstitute said...

What a really awesome post this is. Truly, one of the best posts I've ever witnessed to see in my whole life. Wow, just keep it up.
data scientist course in malaysia

Tutorials said...

Nice article with valuable information. Thanks for sharing.

AWS Training in Chennai | AWS Training institute in Chennai

traininginstitute said...

Excellent effort to make this blog more wonderful and attractive.
data science course in malaysia

Professional Course said...

Happy to chat on your blog, I feel like I can't wait to read more reliable posts and think we all want to thank many blog posts to share with us.

Cyber Security Training in Bangalore

Unknown said...

I want to leave a little comment to support and wish you the best of luck.we wish you the best of luck in all your blogging enedevors
cloud computing course in noida

business analytics course in chennai with placement said...

I am looking for and I love to post a comment that "The content of your post is awesome" Great work! Data Scientist Course in Chennai

Data Science said...

Extremely overall quite fascinating post. I was searching for this sort of data and delighted in perusing this one. Continue posting. A debt of gratitude is in order for sharing.data scientist course in warangal

Unknown said...

This is a good post. This post gives truly quality information. I’m definitely going to look into it. Really very useful tips are provided here. Thank you so much. Keep up the good works data science course in mysore

Bhuvankumar said...

I am sure it will help many people. Keep up the good work. It's very compelling and I enjoyed browsing the entire blog.
Business Analytics Course in Bangalore

Nathan said...


Very educating blog, got lot of information thank you.
Data Scientist Course in Jaipur

Tech Institute said...

I read your excellent blog post. It's a great job. I enjoyed reading your post for the first time, thank you.
Data Science Institutes in Bangalore

Tech Institute said...

Very informative blog! There is so much information here that can help thank you for sharing.
Data Science Syllabus

Professional Course said...

I am more curious to take an interest in some of them. I hope you will provide more information on these topics in your next articles.

Data Science in Bangalore

Excelr Tuhin said...

Hi! This is my first visit to your blog! We are a team of volunteers and new initiatives in the same niche. Blog gave us useful information to work. You have done an amazing job!

satta

satta matta matka

Unknown said...

Extremely overall quite fascinating post. I was searching for this sort of data and delighted in perusing this one. Continue posting. A debt of gratitude is in order for sharing.data scientist course in bhubaneswar

subham kumar said...

You have done a great job . keep up the good work .thanks for sharing nice information . Java Vogue have good examples on java . 

traininginstitute said...

What a really awesome post this is. Truly, one of the best posts I've ever witnessed to see in my whole life. Wow, just keep it up.
full stack developer course

Data Science said...

Extremely overall quite fascinating post. I was searching for this sort of data and delighted in perusing this one. Continue posting. A debt of gratitude is in order for sharing.business analytics course in kolhapur

Professional Course said...

It was a wonderful opportunity to visit this type of site and I am happy to hear about it. Thank you very much for giving us the opportunity to have this opportunity.

Data Analytics Course in Durgapur

360DigiTMG said...

In the wake of perusing your article, I was astounded. I realize that you clarify it well overall. What's more, I trust that different perusers will likewise encounter how I feel in the wake of perusing your article.
data analytics training in hyderabad

Unknown said...

I want to leave a little comment to support and wish you the best of luck.we wish you the best of luck in all your blogging enedevors
data scientist course in delhi

Deekshitha said...

Informative blog
data science course in ludhiana

Professional Course said...

It's a smart blog. I mean it seriously. You have so much knowledge on this subject and so much passion. He also knows how to get people to join him, obviously from the answers.

Data Analytics Course in Nagpur

Unknown said...

I want to leave a little comment to support and wish you the best of luck.we wish you the best of luck in all your blogging enedevors
data analytics course in thiruvananthapuram

Professional Course said...

It's a smart blog. I mean it seriously. You have so much knowledge on this subject and so much passion. He also knows how to get people to join him, obviously from the answers.

Data Science Course in Nagpur

Deekshitha said...

Informative blog
data analytics course in ludhiana

360DigiTMGAurangabad said...

nice blog!! i hope you will share a blog on Data Science.
data scientist course in aurangabad

rstrainings said...


I am really happy to say it’s an interesting post to read . I learn new information from your article , you are doing a great job . Keep it up

Devops Training in Hyderabad

Hadoop Training in Hyderabad

Python Training in Hyderabad

Tableau Training in Hyderabad

Selenium Training in Hyderabad

Informatica Training in Hyderabad

Professional Course said...

Thank you for posting this information. I just want to let you know that I just visited your site and find it very interesting and informative. I look forward to reading most of your posts.

Data Science Course in Patna

Professional Course said...

I always search online for articles that can help me. Obviously, there is a lot to know about this. I think you made a few good points about the features as well. Keep up the good work!

Data Scientist Course in Ernakulam

Data Science said...

Extremely overall quite fascinating post. I was searching for this sort of data and delighted in perusing this one.
Continue posting. A debt of gratitude is in order for sharing.
data science training in gwalior

Data Analytics Courses in Bangalore said...

I want to leave a little comment to support and wish you the best of luck.we wish you the best of luck in all your blogging enedevors
data science course delhi

Anonymous said...

I want to leave a little comment to support and wish you the best of luck.we wish you the best of luck in all your blogging enedevors
data science training in varanasi

Professional Course said...

I want to thank you for your efforts in writing this article. I look forward to the same best job from you in the future.

Data Science in Bangalore

Unknown said...

I want to leave a little comment to support and wish you the best of luck.we wish you the best of luck in all your blogging enedevors
data scientist course in trivandrum

Unknown said...

I want to leave a little comment to support and wish you the best of luck.we wish you the best of luck in all your blogging enedevors
business analytics course in faridabad

Unknown said...

I want to leave a little comment to support and wish you the best of luck.we wish you the best of luck in all your blogging enedevors
data science course in trivandrum

Data Science said...

Extremely overall quite fascinating post. I was searching for this sort of data and delighted in perusing this one.
Continue posting. A debt of gratitude is in order for sharing.
data science course in kolhapur

kumar said...

It is perfect time to make some plans for the future and it is time to be happy. I've read this post and if I could I desire to suggest you some interesting things or suggestions. Perhaps you could write next articles referring to this article. I want to read more things about it! digital marketing course

business analytics course in chennai said...

There is obviously a lot to know about this. I think you made some good points in Features also. Keep working, great job! Data Scientist Course in Dombivli

data science said...

This is my first time i visit here. I found so many entertaining stuff in your blog, especially its discussion. From the tons of comments on your articles, I guess I am not the only one having all the leisure here! Keep up the good work. I have been meaning to write something like this on my website and you have given me an idea. PMP Course in Malaysia

kumar said...

Took me time to understand all of the comments, but I seriously enjoyed the write-up. It proved being really helpful to me and Im positive to all of the commenters right here! Its constantly nice when you can not only be informed, but also entertained! I am certain you had enjoyable writing this write-up. data analytics course

kumar said...

Writing style and getting good compliments on the article is quite hard, to be honest.with But you've done it so calmly and with so cool feeling and you've nailed the job. This article is possessed with style and I am giving good compliment. Best! diploma in digital marketing malaysia

deekshitha said...

Get the best Data Science online course at the comfort of your home. Flexible timings, Best industry trainers, and meticulously crafted curriculum. Avail now!!!!data analytics course in varanasi

Technical Knowledge said...

You have done a great job and will definitely dig it and personally recommend to my friends. Thank You.
Data Science Online Training

Diksha Gupta said...

I like your post. I appreciate your blogs because they are really good. Please go to this website for Data analyst course in Bangalore. These courses are wonderful for professionals.

Professional Career Technology said...

With decision making becoming more and more data-driven, learn the skills necessary to unveil patterns useful to make valuable decisions from the data collected. Also, get a chance to work with various datasets that are collected from various sources and discover the relationships between them. Ace all the skills and tools of Data Science and step into the world of opportunities with the Best Data Science training institutes in Bangalore.


Data Science Course in Bangalore with Placement

Career Academic institute said...

Enroll in the Data Science course near me to learn the handling of huge amounts of data by analyzing it with the help of analytical tools. This field offers ample job profiles to work as a Data Architect, Data Administrator, Data Analyst, Business Analyst, Data Manager, and BI Manager. Step into an exciting career in the field of Data Science and achieve great heights by acquiring the right knowledge and skills to formulate solutions to business problems.
Best Data Science Training institute in Bangalore

Professional Academic Institute said...

The new wave of innovation that is changing the way people do business is called data science. Gain expertise in organizing, sorting, and transforming data to uncover hidden patterns Learn the essential skills of probability, statistics, and machine learning along with the techniques to break your data into a simpler format to derive meaningful information. Enroll in Data science in Bangalore and give yourself a chance to power your career to greater heights.

Data Science Course in Jaipur

Educational Courses said...

Advance your technical skills required to crack huge datasets to bring out new possibilities from data. Join the Data Science institutes in Bangalore and get access to top industry trainers, LMS, live projects, assignments, and mock interviews to skyrocket your career in the ever- evolving field of Data Science.

Data Science Course in Delhi

Professional Courses and Training said...

Learn to use analytics tools and techniques to manage and analyze large sets of data from Data Science training institutes in Bangalore. Learn to take on business challenges and solve problems by uncovering valuable insights from data. Learn from the comprehensively designed curriculum by the industry experts and work on live projects to sharpen your skills.

Data Scientist Course in Delhi

data science said...

Are you looking for a Data Science course that will aid you in your career growth. 360DigiTMG is the right place for you. Enroll now for a better tomorrow.
business analytics course in lucknow

Indeed Visa said...

Thanks for posting the best information
Need Visa Service Visit Australian Tourist Visa

Indeed Visa said...


It is very helpful and very informative
Need Visa Service Visit Australian Tourist Visa

Swarnalatha said...

"If you are also one of them and want to know what the companies demand from the data scientists to do in their organization, you have come to the right place.data science course in kolkata"

Courses said...

The blog and data is excellent and informative as well your work is very good and I appreciate well hopping for some more informative posts.
Business Analytics Course in Gurgaon

Data science course said...

It has lessened the load on the people as it works on the data patterns that minimize the data volumedata science course in ghaziabad.

Lavanya said...

Data Analytics jobs are rising in the market. Become a Data Analyst with the 360DigiTMG Data Analytics course.
Data Analytics Course in Bangalore

«Oldest ‹Older   201 – 262 of 262   Newer› Newest»