1 Version 4.0 -- 1/15/89 bcp
______________________________________________________________________
NAME: bcp
FUNCTION:
Copies a database table to or from a host file in a user-
specified format.
SYNTAX:
bcp [[database_name.]owner.]table_name {in | out} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n] [-c]
[-t field-terminator] [-r row-terminator]
bcp Version 4.0 -- 1/15/89 2
______________________________________________________________________
[-U username] [-P password] [-I interface] [-S server] [-v]
EXAMPLES:
In the following example, bcp copies data from the publishers
table to a file for later reloading into SQL Server. Defaults
were accepted for all prompts by pressing RETURN.
bcp pubs..publishers out publ_out
Password:
Enter the file storage type of field pub_id [char]:
Enter prefix-length of field pub_id [0]:
Enter length of field pub_id [4]:
Enter field terminator [none]:
Enter the file storage type of field pub_name [char]:
Enter prefix-length of field pub_name [1]:
3 Version 4.0 -- 1/15/89 bcp
______________________________________________________________________
Enter length of field pub_name [40]:
Enter field terminator [none]:
Enter the file storage type of field city [char]:
Enter prefix-length of field city [1]:
Enter length of field city [20]:
Enter field terminator [none]:
Enter the file storage type of field state [char]:
Enter prefix-length of field state [1]:
Enter length of field state [2]:
Enter field terminator [none]:
Do you want to save this format information in a file? [Y-n] y
Host filename [bcp.fmt]: pub_form
Starting copy...
bcp Version 4.0 -- 1/15/89 4
______________________________________________________________________
3 rows copied.
Clock Time (ms.): total = 0 Avg = 0 (3.00 rows per sec.)
The results go into publ_out.
To copy this data back into SQL Server using the saved format
file, pub_form, you would use the command:
bcp pubs..publishers in publ_out -f pub_form
PARAMETERS:
database_name - is optional if the table being copied is in your
default database. Otherwise, you must specify a database
name.
owner - is optional only if you own the table being copied. If
5 Version 4.0 -- 1/15/89 bcp
______________________________________________________________________
no owner is specified and you do not own a table of that
name, the command fails.
table_name - the name of the database table you want to copy.
{in | out} - the direction of the copy. In indicates a copy from
a file into the database table, while out is a copy to a file
from the database table.
datafile - the full path name of a host file. The path name can
be from 1 to 255 characters in length. It can also indicate
a tape drive name.
-m maxerrors - the maximum number of errors before the copy is
aborted. Each row that can't be built by bcp is thrown out
and counted as one error. The default, used if this option
is not included, is 10.
-f formatfile - the full path name of a file with stored
responses from a previous use of bcp on the same table; crea-
tion of the format file is optional. Use this option only
when you have already created a format file that you want to
bcp Version 4.0 -- 1/15/89 6
______________________________________________________________________
use for a copy in or out. After you answer bcp's format
questions, it will ask you if you want to save your answers
in a format file. The default file name is bcp.fmt. The bcp
program can refer to a format file when copying data, so that
you do not have to duplicate your previous format responses
interactively. If this option is not used, bcp will query
you for format information interactively.
-e errfile - the full path name of an error file where bcp
stores any rows that it was unable to transfer from the file
to the database. Error messages from the bcp program go to
the user's workstation. If this option is not used, no error
file is created.
-F firstrow - the number of the first row to copy (default is
the first row).
-L lastrow - the number of the last row to copy (default is the
last row).
-b batchsize - the number of rows per batch of data copied (the
7 Version 4.0 -- 1/15/89 bcp
______________________________________________________________________
default is to copy all the rows in one batch).
-n - perform the copy operation using the data's native (data-
base) datatypes as the default. This option does not prompt
for each field; it uses the default values.
-c - perform the copy operation with character type as the
default. This option does not prompt for each field; it uses
char as the default storage type, no prefixes, \t as the
default field separator, and \n as the default row termina-
tor.
-t field-terminator - specify the default field terminator.
-r row-terminator - specify the default row terminator.
-U username - allows the user to specify a login name.
-P password - allows the user to specify a password. If the -P
option is not given, bcp prompts for a password. If the -P
option is given at the end of the command line without any
password, bcp uses the default password (NULL).
-I interface - allows the user to specify the name and location
bcp Version 4.0 -- 1/15/89 8
______________________________________________________________________
of the interfaces file that can (optionally) be searched as
part of the process of connecting to the SQL Server. The
named file contains the name and network address of every
available SQL Server on the network. If this option is not
used, bcp looks for a file named interfaces.
-S server - allows the user to specify the name of the
SQL Server with which to connect. This is the name that
SQL Server looks up in the interfaces file.
-v - reports the current version of the bcp program.
COMMENTS:
o The bcp bulk copy program provides a convenient method for
transferring data between a database table and a host file or
tape. Data copied IN from a file is appended to an existing
database table; data copied OUT to a file overwrites any previ-
ous contents of the file.
9 Version 4.0 -- 1/15/89 bcp
______________________________________________________________________
o The bcp program performs a high-speed data transfer. It is
capable of reading or writing files in a wide variety of for-
mats.
o The -n (native format) and -c (character format) command line
options each provide specific default formats for the data
being copied. Native format provides the most compact storage;
character format creates ASCII files, with tabs between
columns.
o When neither of the -n or -c option is used, bcp will query the
user for information about each column in the table to be
copied and asks whether you want to save your format informa-
tion in a file.
o Upon completion, bcp informs you of the number of rows of data
successfully copied, the number of rows (if any) that it could
not copy, the total time the copy took, the average amount of
time it took to copy one row (given in milliseconds), and the
bcp Version 4.0 -- 1/15/89 10
______________________________________________________________________
number of rows copied per second.
o In order to use bcp, you must have a valid SQL Server account
and the appropriate permissions on the database tables and
files.
o The bcp program is optimized to load data into tables that do
not have indexes associated with them. When you copy data into
a table that does have one or more indexes, a slower version of
bcp is automatically used.
However, the fast version of bcp inserts data without logging
it, and requires the System Administrator or Database Owner to
first set the select into/bulkcopy option on with the
sp_dboption procedure. If the option is not set on and a user
tries to copy data into a table that has no indexes, SQL Server
generates an error message. You don't need to set this option
in order to copy data out to a file, or in order to copy data
11 Version 4.0 -- 1/15/89 bcp
______________________________________________________________________
in to a table that does have indexes. A bulk copy in to tables
that have indexes always uses the slower version of bcp, and
row inserts are logged.
WARNING ________________________________________________________
| |
| Because bulk copy logs inserts into a table that has |
| indexes, the log can grow very large. You can truncate |
| the log with DUMP TRANsaction after the bulk copy com- |
| pletes, after you have backed up your database with DUMP |
| DATABASE. |
|_______________________________________________________________|
While the select into/bulkcopy option is on, you are not allowed to
dump the transaction log. Issuing the DUMP TRANsaction statement
produces an error message instructing you to use DUMP DATABASE
bcp Version 4.0 -- 1/15/89 12
______________________________________________________________________
instead.
This table shows which version of bcp will be used when copying IN,
the necessary settings for the select into/bulkcopy option, and
whether the transaction log is kept and dumpable.
select into/bulkcopy
ON OFF
fast bcp OK
(no indexes on target table) DUMP TRAN prohibited prohibited
slow bcp OK OK
(one or more indexes) DUMP TRAN prohibited DUMP TRAN OK
By default, the select into/bulkcopy option is off in newly created
databases. To change the default situation, turn this option on in
the Model Database.
13 Version 4.0 -- 1/15/89 bcp
______________________________________________________________________
The performance penalty for copying data into a table that has
indexes in place may be severe. If you are copying in data that
will increase the size of the table by 25%, it may be faster to drop
all the indexes beforehand with DROP INDEX, copy the data into the
table, and then recreate the indexes.
WARNING _____________________________________________________________
| |
| Your database must have free space equal to at least 120% of |
| the size of the table in order to build or rebuild a clustered |
| index. |
|____________________________________________________________________|
o When using bcp, the user is prompted for a password, if it was not
supplied with the -P option.
bcp Version 4.0 -- 1/15/89 14
______________________________________________________________________
o After the password has been given, bcp prompts for information on
each field in the specified table. Each prompt displays a default
value, in brackets, which is accepted by pressing the RETURN key.
The prompts include: the storage type a prefix-length the field
length a field terminator
o The defaults in brackets at each prompt represent reasonable
defaults for the field in question. These default values depend on
the datatype of the field. The following table shows the defaults
and possible responses.
15 Version 4.0 -- 1/15/89 bcp
______________________________________________________________________
Prompt Default Provided Possible Responses
Storage Type Database storage char to create or
type for most read an ASCII file;
fields; any SQL Server data-
char for varchar; type where implicit
binary for var- conversion is sup-
binary. ported.
Prefix Length 0 for fields defined with datatype 0 if no prefix is
(not storage type) char desired; defaults
and all fixed-length datatypes, are recommended in
1 for most other datatypes all other cases.
2 for binary and varbinary
saved as char
4 for text and image
bcp Version 4.0 -- 1/15/89 16
______________________________________________________________________
Length Defined length for Default values, or
char and varchar. greater, are recom-
Defined length * 2 mended.
for binary and var-
binary saved as
char. Maximum
length needed to
avoid truncation or
data overflow for
all other datatypes.
17 Version 4.0 -- 1/15/89 bcp
______________________________________________________________________
Field Terminator None. Up to 30 ASCII char-
acters, or one of
the following:
\t tab
\n newline
\r carriage return
\0 null terminator
\\ backslash
o Data can be copied into a file either as its native (database) data-
type, or as any datatype for which implicit conversion is supported
for the datatype in question. See the DB-Library manual page for
bcp Version 4.0 -- 1/15/89 18
______________________________________________________________________
dbconvert for details.
Here are the default storage datatypes that correspond to SQL Server
datatypes, and the legal abbreviations:
Table DatatypeStorageType
char c[har]
varchar c[har]
text T[ext]
binary x
varbinary x
image I[mage]
int i[nt]
smallint s[mallint]
tinyint t[inyint]
float f[loat]
19 Version 4.0 -- 1/15/89 bcp
______________________________________________________________________
bit b[it]
money m[oney]
datetime d[ate]
timestamp x
†Brackets ([]) indicate that you may use the initial character,
or the beginning characters of the word, i.e. for bit you could
use b, bi or bit.
timestamp data is treated as binary(8).
o Fields defined in the database as char and binary are always padded
with spaces to the full length defined in the database.
o When storing fields (except char and binary fields) as char instead
of their database datatypes, they will take less file storage space
with the default length and a prefix or a terminator. bcp can use
either a terminator or a prefix to determine the most efficient use
bcp Version 4.0 -- 1/15/89 20
______________________________________________________________________
of storage space. The maximum amount of storage space required for
each field is suggested by bcp as the default.
A length prefix is a 1, 2, or 4-byte integer which represents the
length of each data value, and immediately precedes each data value
in the host file. A terminator is any character or string of char-
acters.
The tables below show the interaction of prefix-lengths, termina-
tors, and field length on the information in the file. ``P'' is
used to indicate the prefix in the stored table; ``T'' indicates the
terminator, and appended spaces are shown with dashes, ``-''.
``...'' indicates that the pattern would repeat for each field. The
field length is 8 for each column, and we're storing the 6-character
field ``string'' each time.
SQL Server char data
21 Version 4.0 -- 1/15/89 bcp
______________________________________________________________________
Prefix-length = 0 Prefix-length 1, 2 or 4
No terminator string--string--... Pstring--Pstring--...
Terminator string--Tstring--T... Pstring--TPstring--T...
Other datatypes converted to char storage
Prefix-length = 0 Prefix-length 1, 2 or 4
No terminator string--string--... PstringPstring...
Terminator stringTstringT... PstringTPstringT...
o Note that the file storage type and length of a column do not have
to be the same as the type and length of the column in the database
table. (If types and formats being copied in are incompatible with
the structure of the database table, the copy fails.)
bcp Version 4.0 -- 1/15/89 22
______________________________________________________________________
o User datatypes are copied as their base datatype or as any datatype
for which implicit conversion is supported.
o For char or varchar data, any length value is accepted.
o If data in varchar and varbinary fields is longer than the length
you specify for copy out, the data in the file is silently truncated
at the specified length.
o A terminator string can be up to 30 characters long; the most common
terminators are a tab (entered as ``\t'' and used for all columns
except the last one), a newline (entered as ``\n'' and used for the
last field in a row). Other terminators are: \0 (the null termina-
tor) and \\ (backslash) and \r (carriage return).
o When a terminator or prefix is present, it affects the actual length
of data transferred. If the length of an entry being copied out to
a file is less than the storage length, it is followed immediately
by the terminator, or the prefix for the next field. The entry is
23 Version 4.0 -- 1/15/89 bcp
______________________________________________________________________
not padded to the full storage length (char and binary data is
returned from SQL Server already padded to the full length).
o When copying in from a file, data is transferred until either the
number of bytes indicated have been copied or the terminator is
encountered. Once a number of bytes equal to the file storage
length has been transferred, the rest of the data is flushed until
the terminator is encountered. When no terminator is used, the
table storage length is strictly observed.
o To determine the file storage length, a good rule of thumb is to
consider it to be the maximum amount of data to be transferred for
the column, plus terminators and/or prefixes.
o When data is copied into a table, any defaults defined for columns
and user datatypes are observed. However, rules and triggers are
ignored in order to load data at the fastest possible speed.
o For the most efficient use of space when copying out to a file,
bcp Version 4.0 -- 1/15/89 24
______________________________________________________________________
accept the default prompts: copy all data in their table datatypes,
use prefixes as indicated, do not use terminators, and accept the
default lengths.
o If you hand-created input scripts for use as format files with pre-
4.0 bcp, you can use the bcptrans program to create new format files
for use with the new bcp. The syntax of bcptrans is:
bcptrans [[database_name.]owner.]table-name [ -3] {in | out} file_name [errors]
The conversion program produces two files: bcptrans.cmd contains the
appropriate command line for the new bcp program. bcptrans.fmt con-
tains the appropriate format file for the new bcp program.
These two files can then be used as input to the new bcp.
The -3 flag to bcptrans must be used to convert an old format file
when you are planning to copy IN an old datafile you created using the
25 Version 4.0 -- 1/15/89 bcp
______________________________________________________________________
default type and length for float and datetime data. (Otherwise, the
format file bcptrans creates will use the new default lengths for
float and datetime data, which have been changed from 20 characters to
25 characters for float, and 26 characters for datetime). Do not use
the -3 flag for converting format files for any data that does not
match this specification.
RETURNS:
This routine returns 0 upon successful completion. Otherwise it
returns 1.