Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Replacing BCP with SQLBulkLoad


Replacing BCP with SQLBulkLoad

Author
Message
s_popovski
s_popovski
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 28
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/spopovski/replacingbcpwithsqlbulkload.asp



DavidCameron
DavidCameron
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 1
This article assumes that XML is the way of the future, which is a false assumption.

XML has some significant problems.
1. Performance. I'm not talking about file size (and the resulting poor performance over networks), but about that fact that parsing XML is an intensive process. For one thing, you must read the entire data structure into memory because you must read to the closing tag. CSV has no such problems.
2. Interoperability. XML only works with new stuff and something that can parse XML. Sure it crosses platforms, but you'll find that CSV as a format is even more accepted.

Even one of the co-authors of the XML 1.0 standard has pointed out some problems with XML.

See
http://slashdot.org/article.pl?sid=03/03/18/0712248&mode=thread&tid=95&tid=156

That is not to say that XML doesn't have its place. Its place is in small applications that don't deal with much data, and/or have complicated data structures that need to be represented with XML.

My impression is that XML is touted as the wave of the future by an increasingly small number of people these days.



Andy Warren
Andy Warren
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: Moderators
Points: 7233 Visits: 2679
Im not much of an XML fan either, but in many cases the extra overhead is worth what you get out of it. Using the DOM requires you to read the entire file (an awesomely slow process with large files, I watched a demo where the memory usage ticked upward...and upward...forever), but SAX and maybe the .Net reader are designed to be lighter. CSV's are ok, but it's not very rich. My favorite data transfer format is MS Access!

Andy
http://www.sqlservercentral.com/columnists/awarren/

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
j-hardin
j-hardin
SSC Journeyman
SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)

Group: General Forum Members
Points: 80 Visits: 1
Doesn't anyone translate these articles into proper English before posting them? The grammatical errors in this article drove me nuts.



cppwiz
cppwiz
SSC-Enthusiastic
SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)

Group: General Forum Members
Points: 184 Visits: 470
This article could have been much better if it was edited and proof-read before posting. I gave up trying to decipher the spelling mistakes and errors, but I think the idea for the article was good.

I didn't know SQLXML had this functionality, but I wouldn't trust the code samples given in the article because of the poor English.



Edited by - cppwiz on 07/21/2003 08:53:54 AM



Thomas LeBlanc
Thomas LeBlanc
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3792 Visits: 901
I got an error trying this. I 3 column table with hr, Source, and Description as the column headings:

hr
0x800401F3

Source
ODSOLE Extended Procedure

Description
Invalid class string

Thanks,
ThomBeaux

Thomas LeBlanc, MCITP DBA 2005, 2008 & MCDBA 2000
http://thesmilingdba.blogspot.com/
K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (6.8K reputation)

Group: Moderators
Points: 6816 Visits: 1917
quote:
Inattention using master..xp_cmdshell can seriously endanger SQL Server security


This article rightfully points out that you tend to avoid xp_cmdshell for security reasons, but then goes and uses the sp_OA* procedures, which you tend to avoid for security and stability reasons. Therefore, I don't think the author can cite security as a reason for using SQLBulkLoad over bcp.



K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1


Edited by - bkelley on 07/21/2003 09:34:06 AM

K. Brian Kelley
@‌kbriankelley
Andy Warren
Andy Warren
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: Moderators
Points: 7233 Visits: 2679
My fault on the English - we haven't decided what to do just yet. Clearly if can't follow the article it's of no value, yet we don't have time to rewrite content that comes in from readers who use English as a second language.

Andy
http://www.sqlservercentral.com/columnists/awarren/

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Mr.No
Mr.No
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 1
I think the BULK INSERT statement is a good substitute to bcp. You avoid teh use of xp_cmdshell and also you avoid the overhead of having each field around tags. I personally think the best data interchange format is a pipe or comma delimited file. The question of portability doesn't arise as even with an XML file you still have to match the various tags. That said, XML does seem interesting and until now I haven't been convinced that it is the right format for transfer of large volume of data



K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (6.8K reputation)

Group: Moderators
Points: 6816 Visits: 1917
quote:
I think the BULK INSERT statement is a good substitute to bcp.


From a performance perspective, it should be slightly faster. BULK INSERT runs in the memory context of SQL Server whereas bcp does not. Both of these would be faster than DTS and the described method here.


K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

K. Brian Kelley
@‌kbriankelley
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search