A Comparison of Various SQL Server Bulk Insert Options in Microsoft .NET

Last month, I worked on a requirement of inserting bulk data into SQL Server database. I evaluated different solutions available in .NET for this purpose, and finally came up with the following comparison chart, which will be useful when someone takes up this common task in their project -
Passing Table Value Types
SQL Server’s Xml OPENROWSET
SQL Data Adapter
SQL Bulk Copy
Make Database Calls in a Loop
Feature Set
Can perform CRUD operations on any related database objects in a single client transaction.
Same as Table Value Types.
Same as Table Value Types.
Supports only Insert operations on one table.
Same as Table Value Types.
Ease of Implementation
Tedious and laborious, but quite repetitive.
So much easier when compared to Table Value Types.
Simpler than the first 2.
Simpler than the first 2.
Simplest of all of them.
Type Safety
Yes
No
Yes (with some difficulty)
Yes (with some difficulty)
Yes
Performance (Considering at least 5 records)
Only inferior to SQL Bulk Copy.
Better than SQL Data Adapter and Loops.
Better than Loops.
Best of all.
Worst performer.
Database Agnostic
No, too much dependency starting from c# code to database.
No, but only database code has dependency.
No, but only C# code has dependency.
No, but only C# code has dependency.
Yes
When will I Recommend?
When performance and maintainability are more important than anything else.
When you want all the features only sacrificing type safety.
A highly compromised solution, so I wouldn’t recommend.
When simple and fast dumping of bulk data is required.
When the bulk update is for less than 5 records.


Comments

Post a Comment

Popular posts from this blog

Chrome Extension - Tab Keeper

Opening too many tabs is one of the biggest memory hogs for the Chrome browser. In my search to find the most elegant solution to this problem, I stumbled upon Jacob Singh's Tab Wrangler extension. It is perfect for my needs, except for the fact that it doesn't store tabs in a feel secure way and sync across all my computers. I didn't find a better way to achieve this, except using chrome Bookmarks. I have modified the code to support this feature, so users can now optionally save all the closed tabs in Other bookmarks  folder in chrome. Recent items are stored in Current folder, and older items are archived based on max tabs setting, to  History folder within different timestamp folders, each containing previous sets of archived tabs.
Read more

The life of a software developer, who can never write a program as efficiently as his manager wants it, for the first time.

During one of my friendly conversations with my father, who is a layman in terms of software development, he asked me, why I had been working almost for a year to develop a simple application called Payroll, which he had always paid from his pocket directly. He felt that, I must work on improving my efficiency as a programmer, as my manager tells me all the time. My blood was boiled, and I felt like opening the 100s of pages specs documents that undergo 10s of revisions, or the long spread sheets containing 100s of earnings and deductions to explain the complexity. But the fact of the matter is, he doesn’t understand most of them, and I also don’t know everything in them. So I thought, why wouldn’t I explain him through a simple story, why it takes so much time to develop a simple software, in general, and why there is a need to change it quite often? Once upon a time there was a product manager, who found an intriguing opportunity and a huge market potential, to develop an ap
Read more

The Ever Growing Myth of Passing Objects

Last month, one of my colleagues posed me an intriguing question on passing reference types by “ref”. I am writing this blog post, to share my explanation to a larger set of audience. I have coined the following analogy, to explain the concept of passing reference types. Think that, you are playing with a kite (object) attached to a thread (pointer), and one of your friends also wants to play with the same. Pass By Value You attach an additional thread to the same kite (object), hand it over to your friend and both of you are now playing with the same kite. After a while your friend got bored, detached his thread, and attached it to a new kite (a new object). But, you won’t get affected as your thread is still tied to the old kite. Pass By Reference You hand over one end of the same thread to your friend and both of you are now again playing with the same kite. Again, after a while your friend, got bored of the old kite, detached the thread, and attached it to a ne
Read more