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