Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rockybalboa
Starting Member
1 Post |
 Posted - 2008-10-16 : 12:23:54
|
| Hi Guys,I understand that using the sql server native bulk copy command will be far more faster compared to using the SQLBulkCopy (.NET) feature provided by system.data.sqlclient. But in general what would be the best practice and why would you choose one over the other if speed is not the primary issue. |
|
|
afrika
Master Smack Fu Yak Hacker
2706 Posts |
Posted - 2008-10-16 : 16:16:30
|
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspxquote:
Remarks Microsoft SQL Server includes a popular command-prompt utility named bcp for moving data from one table to another, whether on a single server or between servers. The SqlBulkCopy class lets you write managed code solutions that provide similar functionality. There are other ways to load data into a SQL Server table (INSERT statements, for example), but SqlBulkCopy offers a significant performance advantage over them.
|
 |
|
|
mct
Starting Member
4 Posts |
Posted - 2008-10-31 : 19:17:50
|
| hey rocky,You are right, the native BCP is by far the faster way to bulk load data into SQL Server. And I am sure you know that if you are able to use the TABLOCK parameter, then the load time get reduced by nearly half !That said, if speed is not your primary issue, then choosing the native option or the .Net managed code option really depends on where you want to control the data loads in. i.e. do you want to manage the data loads, load errors, logs etc inside C# or VB.Net middle tier components, or do you want to control it in the database. I have had a situation where we had to leverage existing common components like logging, email alerts for loads etc written in C#, so for our data loads of large delimited files, we decided to use the SqlBulkCopy from .Net instead of SQL Server BULK INSERT, and installed the necessary C# classes and methods as CLR SPs / Functions.-Shiva[url]mycodetrip.com[/url] |
|
|
|
|
|
|
|
|
SQL server native BCP vs. ADO.net SQLBulkCopy