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.

 All Forums
 SQL Server 2005 Forums
 .NET Inside SQL Server (2005)
 SQL server native BCP vs. ADO.net SQLBulkCopy

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.aspx

quote:
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.

Go to Top of Page

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]
Go to Top of Page
   

- Advertisement -