FreeTDS Development Group

["Lowden, James K" <LowdenJK AT bernstein.com>]

> From: Daniel Morgan [mailto:danmorg AT sc.rr.com]
> Sent: May 21, 2002 5:29 PM
> 
> What is "bcp"?

"bcp" stands for "bulk copy".  It is the name Sybase gave to a feature of
their server and TDS protocol.  

Normal INSERT statements are first applied to the transaction log and then
applied to the table.  A bcp insertion, OTOH, never touches the transaction
log.  It's applied directly to the table and committed wholesale.  bcp
operations are often an order of magnitude faster than ordinary inserts, and
are easier on the server, too.  

To make bcp possible, Sybase included a feature in the TDS protocol to (1)
alert the server and (2) structure the incoming data such that it's easy for
the server to accept them.  This feature is made accessible to client
applications via a set of bcp functions in db-lib and ct-lib (and ODBC,
IIRC).  The APIs allow the programmer to indicate the name of a file to
load, or to send "rows" of data from variables inside the application.  The
latter feature is especially useful to the kind of numerical/quantitative
programming I do for a living; it's rare that I'm working with under a
million rows.  

Sybase and Microsoft both include a "bcp" utility tool to load/unload the
server.  It's the standard way to import data from another server, for
example.  FreeTDS hasn't had one (we supply the library, you supply the
application), but AIUT Bill Thompson has written us one (Yay, Bill!) so that
gap is about to be filled.  

One sometimes hears of "bcp out" i.e., extracting data to a file with the
bcp utility.  From the server's and protocol's point of view, there no (or
almost no) such thing.  The real advantage of bcp is bypassing the
transaction log and its concomitant locks and I/O, elements that aren't
significant when reading a table.  

AFAIK, bcp is a unique feature in the database world.  Other databases have
ways to load data, of course, but not via the client protocol.  

RTFM for more.  

Regards, 
--jkl