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

36 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

priyanka 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