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();
         
}

66 comments:

for ict 99 said...

great

Priyanka said...

Attend The Data Science Courses From ExcelR. Practical Data Science Courses Sessions With Assured Placement Support From Experienced Faculty. ExcelR Offers The Data Science Courses.
ExcelR Data Science Courses
Data Science Interview Questions

Rajesh Anbu said...

Wonderful blog.. Thanks for sharing informative blog.. its very useful to me..
aws Training in Bangalore
python Training in Bangalore
hadoop Training in Bangalore
angular js Training in Bangalore
bigdata analytics Training in Bangalore
python Training in Bangalore
aws Training in Bangalore

Gowtham said...

Must I say to thank you for sharing the very useful and informative post. I hope a lot of ideas from your blog...
Graphic Design Courses in Chennai
graphic design institute in chennai
Advanced Excel Training in Chennai
Linux Training in Chennai
Oracle Training in Chennai
Oracle DBA Training in Chennai
Power BI Training in Chennai
Tableau Training in Chennai
Pega Training in Chennai
Appium Training in Chennai

ss said...



Excellent blog thank u sharing the useful info.....


data analytics course

data science course


business analytics course



Tuhin said...

keep up the good work. this is an Ossam post. This is to helpful, i have read here all post. i am impressed. thank you. this is our business analytics courses in Mumbai
business analytics courses in Mumbai | https://www.excelr.com/data-science-course-training-in-mumbai

360DigiTMG said...

I feel very grateful that I read this. It is very helpful and very informative and I really learned a lot from it.
360DigiTMG artificial intelligence course in hyderabad

python training in vijayawada said...

This is a wonderful article, Given so much info in it, Thanks for sharing. CodeGnan offers courses in new technologies and makes sure students understand the flow of work from each and every perspective in a Real-Time environmen python training in vijayawada. , data scince training in vijayawada . , java training in vijayawada. ,

Data Science Course said...

I have read your blog its very attractive and impressive. Very systematic indeed! Excellent work!
Data Science Course
Data Science Course in Marathahalli
Data Science Course Training in Bangalore

tejaswini said...

I like viewing web sites which comprehend the price of delivering the excellent useful resource free of charge. I truly adored reading your posting. Thank you!
data science course
data analytics certification
tableau classroom training
360DigiTMG

tejaswini said...

I am really enjoying reading your well written articles. It looks like you spend a lot of effort and time on your blog. I have bookmarked it and I am looking forward to reading new articles. Keep up the good work.
data science course
data analytics certification
tableau classroom training
360DigiTMG

tejaswini said...

It's late finding this act. At least, it's a thing to be familiar with that there are such events exist. I agree with your Blog and I will be back to inspect it more in the future so please keep up your act.
data science course
data analytics certification
tableau classroom training
360DigiTMG

nikhil reddy said...

Thanks For Sharing Content Its Very Much USeful to all Data Science Aspirants

Data Science Training In Hyderabad

Data Science Course In Hyderabad

Unknown said...


The information provided on the site is informative. Looking forward more such blogs. Thanks for sharing .
Artificial Inteligence course in Thiruvananthapuram
AI Course in Thiruvanantapuram

dhishageetha said...


This Blog is really informative!! keep update more about this...
Aviation Academy in Chennai
Air Hostess Training in Chennai
Airport Management Courses in Chennai
Best Aviation Academy in Chennai
Ground Staff Training in Chennai
Air Hostess Academy in Chennai
Airport Management Training in Chennai
Airport Ground Staff Training Courses in Chennai

shanjames said...


Thanks for nice blog regarding DevOps. Keep updating
DevOps Training In Hyderabad

priyash said...

wonderful article. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article. This article resolved my all queries.
Data science Interview Questions
Data Science Course

meritstep Technology said...

Thanks for Sharing This Article.It is very so much valuable content. I hope these Commenting lists will help to my website
servicenow online training
best servicenow online training
top servicenow online training

shanjames said...
This comment has been removed by the author.
shanjames said...

Thank you sharing such wonderful information. keep on updating
Hadoop Training In Hyderabad

Anebellyliza said...

Great blog. it’s really helpful. Thank you so much for this incredible guide, . Keep sharing on updated blog posts.
Machine Learning Training in Hyderabad

Python Training in Hyderabad said...

Thank you a lot for providing individuals with a very spectacular possibility to read critical reviews from this site.
Python Training in Hyderabad
Python Course in Hyderabad
Python Institute in Hyderabad

Priyanka said...

Attend The Data Analytics Courses From ExcelR. Practical Data Analytics Courses Sessions With Assured Placement Support From Experienced Faculty. ExcelR Offers The Data Analytics Courses.
Data Analytics Courses
Data Science Interview Questions

Big Data Analytics Malaysia said...

Superbly written article, if only all bloggers offered the same content as you, the internet would be a far better place..


pmp certification in malaysia

pmp certification

pmp course in malaysia

dataanalyticscourse said...

I am impressed by the information that you have on this blog. It shows how well you understand this subject.
data analytics course
data science course
big data course in malaysia
360DigiTMG
big data analytics training in malaysia

nikhil reddy said...

Expected to form you an almost no word to thank you once more with respect to the decent recommendations you've contributed here.
Machine Learning Training In Hyderabad

tejaswini said...

it's really cool blog. Linking is very useful thing.you have really helped
data science course
360DigiTMG

shanjames said...

Thanks for sharing such a wonderful Articles with us.
DevOps Training In Hyderabad

anudeep said...

Thanks for sharing such a wonderful Articles, I would like to add a little comment best digital marketing institute in hyderabad

nakshatra said...

I am impressed by the information that you have on this blog. It shows how well you understand this subject.
data analytics course
data science course
big data course
big data course
360DigiTMG

datasciencecourse said...

cool stuff you have and you keep overhaul every one of us

Correlation vs Covariance

datasciencecourse said...

Awesome blog. I enjoyed reading your articles. This is truly a great read for me. I have bookmarked it and I am looking forward to reading new articles. Keep up the good work!

Correlation vs Covariance

Avijit said...

What a great article!. I am bookmarking it to read it over again after work. It seems like a very interesting topic to write about.
SEO Services in Kolkata
Best SEO Services in Kolkata
SEO Company in Kolkata
Best SEO Company in Kolkata
Top SEO Company in Kolkata
Top SEO Services in Kolkata
SEO Services in India
SEO Company in India

lionelmessi said...

Excellent article useful to all the aspirants

DevOps Training in Hyderabad
DevOps Course in Hyderabad

reshma said...

thanks for sharing nice information...
devops Training in Hyderabad

eazyclasses said...

A debt of gratitude is in order for the data about call communities. It is consistently incredible to find out about this ever-evolving industry.


Online Teaching Platforms
Online Live Class Platform
Online Classroom Platforms
Online Training Platforms
Online Class Software
Virtual Classroom Software
Online Classroom Software
Learning Management System
Learning Management System for Schools
Learning Management System for Colleges
Learning Management System for Universities

priyash said...

Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article. This article inspired me to read more. keep it up.
Correlation vs Covariance
Simple linear regression

BestTrainingMumbai said...

If all the writers who pen down articles would give a thought to write topic specific articles like you, then more number of readers would read their content.
SAP training in Mumbai
Best SAP training in Mumbai
SAP training institute Mumbai

BestTrainingKolkata said...

I must abide that you are highly trained at influential writing as I am highly convinced to share your views. I am extremely amazed by your excellent writing abilities. Please keep up your good work! SAP training in Kolkata
Best SAP training in Kolkata
SAP training institute in Kolkata

nakshatra said...

I am impressed by the information that you have on this blog. It shows how well you understand this subject.
data analytics course
big data analytics malaysia
big data course

CloudLearn ERP said...

wonderful article. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article. This article resolved my all queries.
Best Data Science training in Mumbai

Data Science training in Mumbai

360digitmgas said...

I like viewing web sites which comprehend the price of delivering the excellent useful resource free of charge. I truly adored reading your posting. Thank you! data science training in coimbatore

Avijit said...

It's genuinely a respect to stumble into enlightening substance like this. You are obviously proficient on this subject and you have one of a kind perspectives to share.


Denial management software
Denials management software
Hospital denial management software
Self Pay Medicaid Insurance Discovery
Uninsured Medicaid Insurance Discovery
Medical billing Denial Management Software
Self Pay to Medicaid
Charity Care Software
Patient Payment Estimator
Underpayment Analyzer
Claim Status

Data Science Bangalore said...

Very interesting blog. Many blogs I see these days do not really provide anything that attracts others, but believe me the way you interact is literally awesome.You can also check my articles as well.

Data Science In Banglore With Placements
Data Science Course In Bangalore
Data Science Training In Bangalore
Best Data Science Courses In Bangalore
Data Science Institute In Bangalore

Thank you..

lionelmessi said...

Good To Share The Content With us
Best AWS Course Training Institute in Hyderabad

jony blaze said...

This is a great inspiring article. I am pretty much pleased with your good work. You put really very helpful information.
Artificial Intelligence Training in Hyderabad
Artificial Intelligence Course in Hyderabad

Priya Rathod said...

Nice information thanks for sharing it’s very useful. This article gives me so much information.
AWS Training in Hyderabad
AWS Course in Hyderabad

Anirban Ghosh said...

Your article is very informative. It's a welcome change from other supposed informational content. Your points are unique and original in my opinion. I agree with many of your points.
SAP training in Kolkata
SAP training Kolkata
Best SAP training in Kolkata
SAP course in Kolkata
SAP training institute Kolkata

Priya Rathod said...

I like your post. Everyone should do read this blog. Because this blog is important for all now I will share this post. Thank you so much for share with us.
DevOps Training in Hyderabad
DevOps Course in Hyderabad

Gaurav said...

This is a wonderful article, Given so much info in it, These type of articles keeps the users interest in the website, and keep on sharing more ... good luck.
data science course in indore

Priyanka said...

Attend The Data Analyst Course From ExcelR. Practical Data Analyst Course Sessions With Assured Placement Support From Experienced Faculty. ExcelR Offers The Data Analyst Course.
Data Analyst Course

nakshatra said...

Awesome blog. I enjoyed reading your articles. This is truly a great read for me. I have bookmarked it and I am looking forward to reading new articles. Keep up the good work!
data analytics course
big data analytics malaysia
big data course

Gaurav said...

I really enjoy simply reading all of your weblogs. Simply wanted to inform you that you have people like me who appreciate your work. Definitely a great post. Hats off to you! The information that you have provided is very helpful.
data science training in indore

sathishkumar said...

Thanks you for sharing information.
python training in bangalore | python online trainng
artificial intelligence training in bangalore | artificial intelligence online training
uipath training in bangalore | uipath online training
blockchain training in bangalore | blockchain online training

priyash said...

Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article. This article inspired me to read more. keep it up.
Correlation vs Covariance
Simple linear regression
data science interview questions

EXCELR said...

Very interesting blog Thank you for sharing such a nice and interesting blog and really very helpful article.
Data Science Course in Hyderabad

xinh said...


trung tâm tư vấn du học canada vnsava



công ty tư vấn du học canada vnsava
trung tâm tư vấn du học canada vnsava uy tín
công ty tư vấn du học canada vnsava uy tín
trung tâm tư vấn du học canada vnsava tại tphcm
công ty tư vấn du học canada vnsava tại tphcm
điều kiện du học canada vnsava
chi phí du học canada vnsava
#vnsava
@vnsava

for ict 99 said...

IEEE Final Year projects Project Centers in India are consistently sought after. Final Year Students Projects take a shot at them to improve their aptitudes, while specialists like the enjoyment in interfering with innovation. A IEEE Domain project Final Year Projects for CSE system development life cycle is essentially a phased project model that defines the organizational constraints of a large-scale systems project.

IT Company Employess Productivity usually increases when a company implements corporate training courses on latest technologies.
corporate training in chennai
It Companies need of Corporate training programme arises due to improvement in technology, need for getting better performance or as part of professional development. corporate training companies in chennai Corporate Training refers to a system of professional development activities provided to educate employees.
corporate training companies in india




Great Article


Digital Marketing Company in Chennai


SEO Company in India

Digital Marketing Agency

SEO Services in India

360digitmgas said...

This is a wonderful article, Given so much info in it, These type of articles keeps the users interest in the website, and keep on sharing more ... good luck. ethical hacking course training in coimbatore

Anebellyliza said...

Highly informative article. This site has lots of information and it is useful for us. Thanks for sharing your views.
Data Science Training in Hyderabad
Data Science Course in Hyderabad

Anebellyliza said...

Great post! Thanks for sharing this amazing post
Artificial Intelligence Training in Hyderabad
Artificial Intelligence Course in Hyderabad

Anebellyliza said...

I really loved reading your blog. I also found your posts very interesting. In fact, after reading, I had to go show it to my friend and he enjoyed it as well!!!!
Machine Learning Training in Hyderabad
Machine Learning Course in Hyderabad

kevin said...

Very good post.
Thank you for providing me with useful information.

Data Science training institute in Hyderabad

Data Science training in Hyderabad

nikhil reddy said...

Hi, Thanks for sharing nice articles...

Data Science Training in Hyderabad

kevin said...

Great blog!!! It is very impressive... thanks for sharing with us...keep posting.

AWS training in Hyderabad

Best AWS training institutes in Hyderabad

kevin said...

Nice post.thank you for sharing this kind of stuff.keep sharing

AWS training in Hyderabad

Best AWS training institutes in Hyderabad