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();
}
97 comments:
great
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
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
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
Excellent blog thank u sharing the useful info.....
data analytics course
data science course
business analytics course
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
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
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. ,
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
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
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
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
Thanks For Sharing Content Its Very Much USeful to all Data Science Aspirants
Data Science Training In Hyderabad
Data Science Course In Hyderabad
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
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
Thanks for nice blog regarding DevOps. Keep updating
DevOps Training In Hyderabad
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
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
Thank you sharing such wonderful information. keep on updating
Hadoop Training In Hyderabad
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
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
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
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
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
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
it's really cool blog. Linking is very useful thing.you have really helped
data science course
360DigiTMG
Thanks for sharing such a wonderful Articles with us.
DevOps Training In Hyderabad
Thanks for sharing such a wonderful Articles, I would like to add a little comment best digital marketing institute in hyderabad
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
cool stuff you have and you keep overhaul every one of us
Correlation vs Covariance
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
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
Excellent article useful to all the aspirants
DevOps Training in Hyderabad
DevOps Course in Hyderabad
thanks for sharing nice information...
devops Training in Hyderabad
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
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
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
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
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
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
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
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
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..
Good To Share The Content With us
Best AWS Course Training Institute in Hyderabad
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
Nice information thanks for sharing it’s very useful. This article gives me so much information.
AWS Training in Hyderabad
AWS Course in Hyderabad
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
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
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
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
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
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
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
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
Very interesting blog Thank you for sharing such a nice and interesting blog and really very helpful article.
Data Science Course in Hyderabad
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
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
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
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
Great post! Thanks for sharing this amazing post
Artificial Intelligence Training in Hyderabad
Artificial Intelligence Course in Hyderabad
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
Very good post.
Thank you for providing me with useful information.
Data Science training institute in Hyderabad
Data Science training in Hyderabad
Hi, Thanks for sharing nice articles...
Data Science Training in Hyderabad
Great blog!!! It is very impressive... thanks for sharing with us...keep posting.
AWS training in Hyderabad
Best AWS training institutes in Hyderabad
Nice post.thank you for sharing this kind of stuff.keep sharing
AWS training in Hyderabad
Best AWS training institutes in Hyderabad
Really nice and interesting post. I was looking for this kind of information and enjoyed reading this one. Keep posting. Thanks for sharing.
click here
Set aside my effort to peruse all the remarks, however I truly delighted in the article. It's consistently pleasant when you can not exclusively be educated, yet in addition, engaged!
360DigiTMG data science course
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.
360DigiTMG
Such a very useful article. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article. 360DigiTMG
This is a great post I saw thanks to sharing. I really want to hope that you will continue to share great posts in the future.
artificial intelligence course in noida
Amazing 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.
Simple Linear Regression
Correlation vs covariance
data science interview questions
KNN Algorithm
Logistic Regression explained
A good blog always comes-up with new and exciting information and while reading I have feel that this blog is really have all those quality that qualify a blog to be a one.
360digitmg
"I can set up my original thought from this post. It gives all around data. A commitment of gratefulness is all together for this essential data for all,
"
HRDF training
Great post i must say and thanks for the information. Education is definitely a sticky subject. However, is still among the leading topics of our time. I appreciate your post and look forward to more. 360DigiTMG
On the off chance that your searching for Online Illinois tag sticker restorations, at that point you have to need to go to the privileged place.
360DigiTMG PMP Certification
I am looking for and I love to post a comment that "The content of your post is awesome" Great work!
Data Science Training in Hyderabad
I was looking at a portion of your posts on this site and I consider this site is really enlightening! Keep setting up..
360DigiTMG supply chain analytics training
By and by I think thrilled I found the web journals.
hrdf claimable training
Great post! I am actually getting ready to across this information, It’s very helpful for this blog. Also great with all of the valuable information you have Keep up the good work you are doing well.
Data Science Training in Hyderabad
Attend The Machine Learning Course Bangalore From ExcelR. Practical Machine Learning course Bangalore Sessions With Assured Placement Support From Experienced Faculty. ExcelR Offers The Machine Learning course Bangalore.
Machine Learning Course Bangalore
Nice blog . Thanks for sharing info
Digital Marketing Courses in Hyderabad With Placements
I liked this blog.. I got some clear information from this blog.. Thanks for taking a time to share this blog...
graphic design courses in tambaram
graphic design courses in Porur
Artificial Intelligence Course in Tambaram
Artificial Intelligence Course in Velachery
Artificial Intelligence Course in porur
I think I have never watched such online diaries ever that has absolute things with all nuances which I need. So thoughtfully update this ever for us.
what is hrdf claimable
Great information. The above content is very interesting to read. This will be loved by all age groups.
list to string python
data structures in python
polymorphism in python
python numpy tutorial
python interview questions and answers
convert list to string python
it’s really nice and meanful. it’s really cool blog. Linking is very useful thing.you have really helped lots of people who visit blog and provide them usefull information.
Data Science Training in Hyderabad
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 USA
Hadoop Training in Hyderabad
Python Training in Hyderabad
Nice blog, I clearly understood what you are saying to this blog, And want to know more about this. keep sharing.
principles of devops
applications of java programming
campaign optimization
web designing career
seo interview questions for freshers
Excellent post for the people who really need information for this technology.data science courses
I appreciate your blog post. Thanks for sharing a very interesting blog, Excellent blog!
skills required for software tester
skills for software tester
robotic process automation scope
use of php language
digital marketing executive interview questions and answers for freshers
This is most informative and also this post most user friendly and super navigation to all posts... Thank you so much for giving this information to me..
data science course
I am glad that i found this page ,Thank you for the wonderful and useful posts and articles enjoyed reading it ,i would like to visit again.
Data Science Course in Mumbai
This is most informative and also this post most user friendly and super navigation to all posts... Thank you so much for giving this information to me..
business analytics courses in aurangabad
Excellent post and I am very happy to read this blog. Keep doing...!
Primavera Training in Chennai
Embedded System Course in Chennai
Embedded Training in Coimbatore
Primavera Course in Chennai
very informative blog
data science training in Pune
This is most informative and also this post most user friendly and super navigation to all posts... Thank you so much for giving this information to me..
data science course in delhi
Cool stuff you have and you keep overhaul every one of us
data analytics training in yelahanka
hello sir,
thanks for giving that type of information. I am really happy to visit your blog.Leading Solar company in Andhra Pradesh
Post a Comment