The Bulk-Copy Program

The proper usage of BCP is

 bcp {  dbtable   query  } {in  out  queryout  format} datafile 

followed by one or more switches.

In this syntax,

  dbtable  

is the database_name + owner + table_name view_name (for example, Northwind.dbo.customers or "Northwind.dbo.customers"):

  database_name  

is the name of the database in which the specified table or view resides. If not specified, this is the default database for the user .

  owner  

is the name of the owner of the table or view.

  table_name   view_name  

is the name of the destination table or view when copying data in to SQL Server ( in ), and the source table when copying data from SQL Server ( out ).

 query 

is a Transact-SQL query that returns a resultset. queryout must also be specified when bulk-copying data from a query.

 in  out  queryout  format 

Specifies the direction of the bulk copy ( in copies from a file in to the database table or view, out copies from the database table or view to a file). queryout must be specified when bulk-copying data from a query. format creates a format file based on the switch specified ( -n , -c , -w , -V , or -N ) and the table or view delimiters. If format is used, the -f option must be specified as well.

  data_file  

is the data file used when bulk-copying a table or view in to or out of SQL Server.

All available Bulk-Copy Program switches are listed in Table 20.1.

Table 20.1. BCP Switches

Switch	Description
-m	The maximum errors to allow before stopping the transfer. The default is 10 . [ “m max_errors ]
-f	The format file used to customize the load or unload data in a specific style. [-f format_file ]
-e	The file to write error messages to. [-e err_file ]
-F	The first row in the data file to start copying from when importing. The default is 1. [-F first_row ]
-L	The last row in the data file to end copying with when importing. The default is , which indicates the last row in the file. [-L last_row ]
-b	The number of rows to include in each committed batch. By default, all data rows in a file are copied in one batch. [-b batch_size ]
-n	Native (database) data type formats are to be used for the data. [-n]
-c	Character data type format is to be used for the data. In addition, \t (tab character) is used as the field separator, and \n (newline character) is used as the row terminator. [-c]
-w	Unicode data type format is to be used for the data. In addition, \t (tab character) is used as the field separator, and \n (newline character) is used as the row terminator. [-w]
-N	Use Unicode for character data and native format for all others. This can be used as an alternative to the “w switch. [-N]
-V	Use data type formats from earlier versions of SQL Server. [-V (60 65 70)]
-6	Don't use this anymore. Use “V instead.
-q	Tells BCP to use quoted identifiers when dealing with table and column names . [-q]
-C	If you are loading extended characters , this switch allows you to specify the code page of the data in the data file. [-C code_page ]
-t	The terminating character(s) for fields. The default is the \t (tab character). [-t field_term]
-r	The terminating character(s) for rows. The default is the \n (newline character). [-r row_term ]
-i	A file for redirecting input into BCP (the response file containing the responses to the command prompts). [-i input_file ]
-o	The file for receiving redirected output from BCP. [-o output_file ]
-a	The network packet size (in bytes) used to send to or receive from SQL Server. Can be between 4096 and 65535 bytes. The default size is 4096. [-a packet_size ]
-S	The SQL Server name to connect to. Local is the default. [-S server_name server_name\instance_name ]
-U	The user account to log in as; this account must have sufficient privileges to carry out either a read or a write of the table. [-U login_id ]
-P	The password associated with the user account. [-P password ]
-T	Make a trusted connection to the SQL Server using the network user/security credentials instead of the login_id/password . [-T]
-v	Display the BCP version information. [-v]
-R	Use the regional format for currency, date, and time data as defined by the locale settings of the client computer. [-R]
-k	Override a column's default and enforce NULL values being loaded into the columns as part of the BCP operation. [-k]
-E	Use the identity values in the import file rather than generating new ones. [-E]
-h	Special "hints" to be used during the BCP operation. These include specifying the following: the sort order of the data file, the number of rows of data per batch, the number of kilobytes of data per batch, to acquire a table-level lock, to check constraints, and to fire insert triggers. [-h hint_type,.. ]

Fundamentals of Exporting and Importing Data

One of the great things about BCP is its ease of use. In this section, the full export and import of data using BCP will be done for Company X's Sales Force Automation requirements specified earlier. The minimum number of options and switches needed to fulfill these requirements will be described and execution examples illustrated . All tables being used here can be found in the "Northwind" sample database supplied by Microsoft in SQL Server 2000. Let's first look at the exporting requirement.

As you might recall, the customers and products tables need to be exported once per week and sent to each salesperson (via e-mail). In turn , these files will be pulled in to the salesperson's Access application. The flat files will also need to be comma delimited. From BCP's point of view, the following things must be specified:

• Full table name ( database+owner + table_name )

   northwind..customers northwind..products 

• Direction of BCP ( OUT in this case because it is exporting data out)

• Data filename (to hold the exported data)

   customers.dat products.dat 

• Server name C814\DBARCH01 for this example ( -S C814\DBARCH01 )

• Username SFA-Admin , the Sales Force Automation Admin ID ( -U SFA-Admin )

• Password; none in this case ( -P )

• Column terminator ( delimiter ) of comma ( “t "," )

• Indication that this should be exported in character data format ( -c )

That's it. So at the command prompt you would see this:

 C:> BCP northwind..customers OUT customers.dat S C814\DBARCH01 U SFA-Admin  P t "," c Starting copy... 91 rows copied. Network packet size (bytes): 4096 Clock Time (ms.):  total       30 

Here's a sample of the data in the customers.dat file:

 ALFKI,Alfreds Futterkiste,Maria Anders,Sales Representative,Obere Str. 57,          Berlin,,12209,Germany,030-0074321,030-0076545 ANATR,Ana Trujillo Emparedados y helados,Ana Trujillo,Owner,Avda. de la         Constitucion 2222,Mexico D.F.,,05021,Mexico,(5) 555-4729,(5) 555-3745 ANTON,Antonio Moreno Taqueria,Antonio Moreno,Owner,Mataderos  2312,         Mexico D.F.,,05023,Mexico,(5) 555-3932 . . . 

This would be followed by the export of products:

 C:> BCP northwind..products OUT products.dat S C814\DBARCH01           U SFA-Admin P t "," c Starting copy... 77 rows copied. Network packet size (bytes): 4096 Clock Time (ms.):  total       21 

The data in products.dat file would be this:

 1,Chai,1,1,10 boxes x 20 bags,18.0000,39,0,10,0 2,Chang,1,1,24 - 12 oz bottles,19.0000,17,40,25,0 3,Aniseed Syrup,1,2,12 - 550 ml bottles,10.0000,13,70,25,0 . . . 

Now, let's look at importing data into SQL Server 2000.

Each salesperson is providing two flat files containing new orders and order details to be imported in to SQL Server 2000 every week. As part of the weekend batch processing, all of these flat files will be merged ( concatenated ) into consolidated files ( new_orders.dat and new_orddtl.dat ). This is what will be imported into the orders and order details tables in the Northwind database. Remember, the new orders keys are assigned by the Access DB application and must be the keys that get loaded in to the orders table (the OrderID in the orders table is an identity column key). The flat files will also be comma delimited. From BCP's point of view, the following things must be specified:

• Full table name ( database + owner + table_name )

   northwind..orders northwind..order_details 

• Direction of BCP ( IN in this case because it is importing data)

• Data filenames (that contain the import data)

   new_orders.dat new_orddtl.dat 

• Here's a sample of the input data file ( new_orders.dat ):

   12010,ERNSH,4,1998-05-05 00:00:00.000,1998-06-02 00:00:00.000,,2,258.6400,       Ernst Handel,Kirchgasse 6,Graz,,8010,Austria 12011,PERIC,2,1998-05-05 00:00:00.000,1998-06-02 00:00:00.000,,2,24.9500,       Pericles Comidas clasicas,Calle Dr. Jorge Cash 321,Mexico D.F.,,05033,Mexico . . . 

• Server name C814\DBARCH01 for this example (-S C814\DBARCH01 )

• Username SFAAdmin , the Sales Force Automation Admin ID ( -U SFAAdmin )

• Password none in this case ( -P )

• Column terminator (delimiter) of comma ( “t "," )

• Indication that this should be imported in character data format ( -c )

• The additional issue with the orders table is that of allowing the keys from the data files to be used instead of the normal identity values being assigned (the primary key OrderID is defined as an identity column); simply use the “E option for this ( -E )

That's it. So at the command prompt you would see this:

 C:> BCP northwind..orders IN new_orders.dat S C814\DBARCH01           U SFAAdmin P t "," c -E Starting copy... 989 rows copied. Network packet size (bytes): 4096 Clock Time (ms.): total     13550 

A quick SELECT * from the orders table shows the success of this operation:

 12018    SIMOB    7    1998-05-06 00:00:00.000    1998-06-03 00:00:00.000    NULL         2    18.4400    Simons bistro    Vinb[ae]ltet 34    Kobenhavn    NULL         1734    Denmark 12019    RICSU    8    1998-05-06 00:00:00.000    1998-06-03 00:00:00.000    NULL         2    6.1900    Richter Supermarkt    Starenweg 5    Genve    NULL         1204    Switzerland 12020    RATTC    1    1998-05-06 00:00:00.000    1998-06-03 00:00:00.000    NULL         2    8.5300    Rattlesnake Canyon Grocery    2817 Milton Dr.    Albuquerque         NM    87110    USA . . . 

In addition, a quick check of the current identity value of this table reassures you that all is well (using OSQL/ISQL/SQL Analyzer):

 Use northwind  go DBCC CHECKIDENT ('orders', NORESEED) Go 

This yields the following:

 Checking identity information: current identity value '12020', current column          value '12020'. DBCC execution completed. If DBCC printed error messages, contact your          system administrator. 

You can now do the import into the order details table.

Note: The -E switch is not needed for this table because no columns in the order details table are defined as identity columns. In addition, the order details table contains a space in its name and must be enclosed in brackets ( [] ) for BCP.

At the command prompt you would have the following:

 C:> BCP northwind..[order details] IN new_orddtl.dat S C814\DBARCH01           U SFAAdmin P t "," c Starting copy... 1000 rows sent to SQL Server. Total sent: 1000 1000 rows sent to SQL Server. Total sent: 2000 1000 rows sent to SQL Server. Total sent: 3000 3243 rows copied. Network packet size (bytes): 4096 Clock Time (ms.): total      5163 

Again, a quick SELECT * from the order details table shows the successful imports:

 12020    64    33.2500    2    2.9999999E-2 12020    66    17.0000    1    0.0 12020    73    15.0000    2    9.9999998E-3 12020    75     7.7500    4    0.0 12020    77    13.0000    2    0.0 . . . 

Great! The Sales Force Automation requirements have been successfully implemented quickly and accurately. That's the beauty and power of using BCP.

The next sections take a look at how BCP can work with basic data representations (character, native, or unicode), the use of format file, and a few other extended BCP capabilities.

File Datatypes

BCP can handle data in one of three forms: character (ASCII), native, or Unicode. You have the choice of which character format is used, depending on the source or destination of the data file.

• The character format ( -c ) is the most commonly used of the three datatypes because it reads or writes using ASCII characters and carries out the appropriate datatype conversion for the SQL Server representations. The CHAR datatype is the default storage type; it uses tabs as field separators and the newline character as the row terminator.

• The native format ( -n ) is used for copying data between SQL Servers. This format allows BCP to read and write using the same datatypes used by SQL Server, leading to a performance gain. This format does, however, render the data file unreadable by any other means.

• The Unicode option ( -w ) uses Unicode characters rather than ASCII characters. The NCHAR datatype is the default storage type; it uses tabs as field separators and the newline character as the row terminator.

The Format File

By using a format file, you can customize the data file created by BCP or specify complex field layouts for data loads. There are two ways to create this format file: interactive BCP and the format switch.

Customizing a Format File Using Interactive BCP

If you do not specify one of the -n , -c , or -w datatype format switches, BCP (in or out) prompts you for the following information for each column in the data set:

• File storage type

• Prefix length

• Field length

• Field terminator

BCP offers a default for each of these prompts that you can accept. If you accept all the defaults, you wind up with the same format file you would have by specifying the native format (with the -n switch). The prompts look like this:

 Enter the file storage type of field au_id [char]:  Enter prefix length of field au_id [0]: Enter length of field au_id [11]: Enter field terminator [none]: 

or

 Enter the file storage type of field OrderID [int]:  Enter prefix length of field OrderID [0]: Enter field terminator [none]: 

By pressing the Enter key at the prompt, you take the default. Alternatively, you can type your own value at the prompt if you know the new value and it is different from the default.

Creating a Format File Using a Switch

By using the format option, you can create a format file without actually transferring any data. Here is an example of creating a format file for the orders table in the Northwind database:

C:> BCP "northwind..orders" format orders.dat “S C814\DBARCH01

 U SFAAdmin P f orders.fmt c 

The format file created looks like this:

 8.0 14 1       SQLCHAR  0       12      "\t"    1    OrderID            "" 2       SQLCHAR  0       10      "\t"    2    CustomerID    SQL_Latin1_General_         CP1_CI_AS 3       SQLCHAR  0       12      "\t"    3    EmployeeID         "" 4       SQLCHAR  0       24      "\t"    4    OrderDate          "" 5       SQLCHAR  0       24      "\t"    5    RequiredDate       "" 6       SQLCHAR  0       24      "\t"    6    ShippedDate        "" 7       SQLCHAR  0       12      "\t"    7    ShipVia            "" 8       SQLCHAR  0       30      "\t"    8    Freight            "" 9       SQLCHAR  0       80      "\t"    9    ShipName      SQL_Latin1_General_         CP1_CI_AS 10      SQLCHAR  0       120     "\t"    10   ShipAddress   SQL_Latin1_General_         CP1_CI_AS 11      SQLCHAR  0       30      "\t"    11   ShipCity      SQL_Latin1_General_         CP1_CI_AS 12      SQLCHAR  0       30      "\t"    12   ShipRegion    SQL_Latin1_General_         CP1_CI_AS 13      SQLCHAR  0       20      "\t"    13   ShipPostalCode SQL_Latin1_General_         CP1_CI_AS 14      SQLCHAR  0       30      "\r\n"  14   ShipCountry    SQL_Latin1_General_         CP1_CI_AS 

Table 20.2 provides a description of the lines and columns in the preceding format file example.

Table 20.2. The Contents of the Format File

Line or Column	Description
1st line	Version of BCP
2nd line	Number of columns
3rd line-1st column	Data field position
3rd line-2nd column	Datatype
3rd line-3rd column	Prefix
3rd line-4th column	Data file field length
3rd line-5th column	Field or row terminator
3rd line-6th column	Column position
3rd line-7th column	Column name
3rd line-8th column	Column collation

You get different format files depending on your table and whether you chose character, native, or Unicode as your data type. As you can see in the preceding example, only the last two columns in the format file relate to the actual table; the remaining columns specify properties of the data file.

File Storage Type

The storage type is the description of how the data is stored in the data file. Table 20.3 lists the definitions used during Interactive BCP and what appears in the format file. The storage type allows data to be copied as its base type (native format), as implicitly converted between types ( tinyint to smallint ), or as a string (in character or Unicode format).

Table 20.3. Storage Datatypes

File Storage Type	Interactive Prompt	Host File Datatype
char	c[har]	SQLCHAR
varchar	c[har]	SQLCHAR
nchar	w	SQLNCHAR
nvarchar	w	SQLNCHAR
text	T[ext]	SQLCHAR
ntext	W	SQLNCHAR
binary	x	SQLBINARY
varbinary	x	SQLBINARY
image	I[mage]	SQLBINARY
datetime	d[ate]	SQLDATETIME
smalldatetime	D	SQLDATETIM4
decimal	n	SQLDECIMAL
numeric	n	SQLNUMERIC
float	f[loat]	SQLFLT8
real	r	SQLFLT4
int	i[nt]	SQLINT
smallint	s[mallint]	SQLSMALLINT
tinyint	t[inyint]	SQLTINYINT
money	m[oney]	SQLMONEY
smallmoney	M	SQLMONEY4
bit	b[it]	SQLBIT
uniqueidentifier	u	SQLUNIQUEID
timestamp	x	SQLBINARY

Note: If the table makes use of user-defined data types, these customized data types appear in the format file as their base data type.

If you are having problems loading certain fields in to your table, you can try the following tricks:

• Copy the data in as CHAR data types and force SQL Server to do the conversion for you.

• Duplicate the table and replace all the SQL Server data types with CHAR or VARCHAR of a length sufficient to hold the value. This trick allows you to further manipulate the data after it is loaded using Transact-SQL.

Prefix Length

For reasons of compactness in native data files, BCP precedes each field with a prefix length that indicates the length of the data stored. The space for storing this information is specified in characters and is called the prefix length.

Table 20.4 indicates the value to specify for prefix length for each of the datatypes.

Table 20.4. Prefix Length Values

Prefix Length	Use
	Non-null data of type bit or numerics ( int , real , and so on). Use this value when no prefix characters are wanted. This value causes the field to be padded with spaces to the size indicated for the field length.
1	Non-null data of type binary or varbinary , or null data with the exception of text , ntext , and image . Use this value for any data (except bit , binary , varbinary , text , ntext , and image ) that you want stored using a character-based datatype.
2	When storing the datatypes binary or varbinary as character-based datatypes. Two bytes of char file storage and four bytes of nchar file storage are required for each byte of binary table data.
4	Use this value for the datatypes text , ntext , and image .

Prefix lengths are likely to exist only within data files created using BCP. It is unlikely that you will encounter a reason to change the defaults BCP has chosen for you.

Field Length

When using either the native or the character data format, you must specify the maximum length of each field. When converting data types to strings, BCP suggests lengths large enough to store the entire range of values for each particular data type. Table 20.5 lists the default values for each of the data formats.

Table 20.5. Default Field Lengths for Data Formats

Datatype	Length ( /c )	Length ( /n )
bit	1	1
binary	Column lengthx2	Column length
datetime	24	8
smalldatetime	24	4
float	30	8
real	30	4
int	12	4
smallint	7	2
tinyint	5	1
money	30	8
smallmoney	30	4
decimal	41	up to 17
numeric	41	up to 17
uniqueidentifier	37	16

NOTE

You must specify a field length that is long enough for the data being stored. BCP error messages regarding overflows indicate that the data value has been truncated in at least one of the fields. If the operation is a load, an overflow error usually results in BCP terminating. However, if you are dumping the data to a file, the data will be truncated without error messages.

The field length value is used only when the prefix length is and you have specified no terminators. In essence, you are doing a fixed-length data copy. BCP uses the exact amount of space stated by the field length for each field; unused space within the field is padded out.

NOTE

Preexisting spaces in the data are not distinguished from added padding.

Field Terminator

If you are not making use of fixed-width fields or length prefixes, you must use a field terminator to indicate the character(s) that separate fields; for the last field in the data row, you must also indicate which character(s) ends the line.

BCP recognizes the following indicators for special characters:

Terminator	Escape Code
Tab	\t
Backslash	\\
Null terminator	\0
Newline	\n
Carriage return	\r

You cannot use spaces as terminators, but you can use any other printable character. Choose field and row terminators that make sense for your data. Obviously, you should not use any character you are trying to load. You must combine the \r and \n characters to get your data into an ASCII data file with each row on its own line.

TIP

By specifying the -t and -r switches, you can override the defaults that appear for the prompts during Interactive BCP.

NOTE

You can specify terminators for data copied in native format. You should be careful if you decide to go this route; the accepted approach is to use length prefixes.

The prefix length, field length, and terminator values interact. In the following examples, T indicates the terminator character(s), P indicates the prefix length, and S indicates space padding.

For data of type char , the data file has the following repeating pattern:

	Prefix Length=0	Prefix Length=1,2,4
No terminator	string S string S	P string SP string S
Terminator	string ST string ST	P string STP strin g ST

For data of other types converted to char , the data file has the following repeating pattern:

	Prefix Length=0	Prefix Length=1,2,4
No terminator	string S string S	P string P string
Terminator	string T string T	P string TP string T

The next few sections examine how to load data into tables when there are differences in column number and layout.

Different Numbers of Columns in File and Table

If you have fewer fields in the data file than exist in the table, you have to " dummy up" an extra line in your format file.

Suppose that you want to load a data file that is missing most of the address information for each customer. By using the format file you created in the section "The Format File," you can still load the data file. Suppose that the data file looks like this:

 WELLI    Wellington Importadora    Jane Graham    Sales (14)555-8122          (14)555-8111 WHITC    White Clover Markets    Donald Bertucci    Owner [phone]         [phone] 

To introduce a dummy value for the missing ones, you must make the following changes to the format file: Make the prefix and data lengths and set the field terminator to nothing ( "" ).

The modified format file will look like this ( custwoaddr.fmt ):

 8.0 11 1       SQLCHAR 0    10    "\t"   1   CustomerID       SQL_Latin1_General_         CP1_CI_AS 2       SQLCHAR 0    80    "\t"   2   CompanyName      SQL_Latin1_General_         CP1_CI_AS 3       SQLCHAR 0    60    "\t"   3   ContactName      SQL_Latin1_General_         CP1_CI_AS 4       SQLCHAR 0    60    "\t"   4   ContactTitle     SQL_Latin1_General_         CP1_CI_AS 5       SQLCHAR 0     0      ""   5   Address          SQL_Latin1_General_         CP1_CI_AS 6       SQLCHAR 0     0      ""   6   City             SQL_Latin1_General_         CP1_CI_AS 7       SQLCHAR 0     0      ""   7   Region           SQL_Latin1_General_         CP1_CI_AS 8       SQLCHAR 0     0      ""   8   PostalCode       SQL_Latin1_General_         CP1_CI_AS 9       SQLCHAR 0     0      ""   9   Country          SQL_Latin1_General_         CP1_CI_AS 10      SQLCHAR 0    48    "\t"  10   Phone            SQL_Latin1_General_         CP1_CI_AS 11      SQLCHAR 0    48  "\r\n"  11   Fax              SQL_Latin1_General_         CP1_CI_AS 

Now BCP can load the data file by using this new format file, with the Address, City, Region, PostalCode, and Country columns containing NULL s for the new rows.

For data files that have more fields than the table has columns, you change the format file to add additional lines of information. Suppose that the Customer data file contains an additional CreditStatus value at the end:

 WELLI    Wellington Importadora    Jack McElreath    Sales Manager    Rua do Mercado,          12    Resende    SP    08737-363    Brazil    (14) 555-8122    NULL 1 WHITC    White Clover Markets    Scott Smith    Owner    305 - 14th Ave. S.         Suite 3B    Seattle    WA    98128    USA    [phone]    (206) 555- 4115 2 

Starting with the same format file as before, you modify it in two important areas: Change the second line to reflect the actual number of values, and add new lines for the extra column in the file that is not in the table. Notice that the column position has a value of to indicate the absence of a column in the table.

Thus the modified format file will look like this ( custwcrdt.fmt ):

 8.0  12  1       SQLCHAR 0    10    "\t"   1   CustomerID       SQL_Latin1_General_         CP1_CI_AS 2       SQLCHAR 0    80    "\t"   2   CompanyName      SQL_Latin1_General_         CP1_CI_AS 3       SQLCHAR 0    60    "\t"   3   ContactName      SQL_Latin1_General_         CP1_CI_AS 4       SQLCHAR 0    60    "\t"   4   ContactTitle     SQL_Latin1_General_         CP1_CI_AS 5       SQLCHAR 0   120    "\t"   5   Address          SQL_Latin1_General_         CP1_CI_AS 6       SQLCHAR 0    30    "\t"   6   City             SQL_Latin1_General_         CP1_CI_AS 7       SQLCHAR 0    30    "\t"   7   Region           SQL_Latin1_General_         CP1_CI_AS 8       SQLCHAR 0    20    "\t"   8   PostalCode       SQL_Latin1_General_         CP1_CI_AS 9       SQLCHAR 0    30    "\t"   9   Country          SQL_Latin1_General_         CP1_CI_AS 10      SQLCHAR 0    48    "\t"  10   Phone            SQL_Latin1_General_         CP1_CI_AS 11      SQLCHAR 0    48    "\t"  11   Fax              SQL_Latin1_General_         CP1_CI_AS  12      SQLCHAR 0     1  "\r\n"   0   CreditStatus     SQL_Latin1_General_   CP1_CI_AS  

The bold italic in the preceding format file indicates the changes made.

These two examples show you the possibilities that the format file offers for customizing the loading and unloading of data.

Renumbering Columns

Using the techniques described previously, you can also handle data file fields that are in different orders than the target tables. All that needs to be done is to change the column order number to reflect the desired sequence of the columns in the table. The fields will then be automatically mapped to the corresponding columns in the table.

For example, suppose that a customer data file that you got from another source system came with the following layout (the fields are in this order):

1. Address

2. City

3. Country

4. PostalCode

5. Region

6. CompanyName

7. ContactName

8. ContactTitle

9. Fax

10. Phone

11. CustomerID

The SQL Server table itself has columns in a different order. To load your data file into this table, you modify the format file to look like this ( custreord.fmt ):

 8.0 11 1       SQLCHAR 0    10    "\t"  11  CustomerID       SQL_Latin1_General_         CP1_CI_AS 2       SQLCHAR 0    80    "\t"  6  CompanyName      SQL_Latin1_General_         CP1_CI_AS 3       SQLCHAR 0    60    "\t"  7  ContactName      SQL_Latin1_General_         CP1_CI_AS 4       SQLCHAR 0    60    "\t"  8  ContactTitle     SQL_Latin1_General_         CP1_CI_AS 5       SQLCHAR 0   120    "\t"  1  Address          SQL_Latin1_General_         CP1_CI_AS 6       SQLCHAR 0    30    "\t"  2  City             SQL_Latin1_General_         CP1_CI_AS 7       SQLCHAR 0    30    "\t"  5  Region           SQL_Latin1_General_         CP1_CI_AS 8       SQLCHAR 0    20    "\t"  4  PostalCode       SQL_Latin1_General_         CP1_CI_AS 9       SQLCHAR 0    30    "\t"  3  Country          SQL_Latin1_General_         CP1_CI_AS 10      SQLCHAR 0    48    "\t"  10  Phone            SQL_Latin1_General_         CP1_CI_AS 11      SQLCHAR 0    48  "\r\n"  9  Fax              SQL_Latin1_General_         CP1_CI_AS 

The bold italic in the preceding format file indicates the changes made. The principal thing to remember with the format file is that all but the last three columns deal with the data file. The last three columns deal with the database table.

Using Views

BCP can also use views to export data from the database. What this means is that an export of data can be a resultset of data from multiple tables (and with distributed queries, even multiple servers).

You can also use a view with BCP to load data back in to tables. However, as is the case with normal Transact-SQL inserts , you can load into only one of the underlying tables at a time.

Loading Image Data

It is actually fairly easy for BCP to load image data into SQL Server. For example, the command to load the data file custlogo.doc (a word document) into the pub_info table in the pubs database using the bcp utility is this:

 C:> bcp pubs..pub_info in c:\temp\custlogo.doc -Usa          -Ppassword Sservername bcp prompts: Enter the file storage type of field pub_id [char]: Enter the prefix length of field pub_id [0]: Enter length of field pub_id [4]: Enter the field terminator [none]: Enter the file storage type of field logo [image]: Enter the prefix length of field logo [4]: 0 Enter length of field logo [4096]: 5578 Enter the field terminator [none]: 

In this example, the data file custlogo.doc will be loaded into column logo, and 5578 is the length of the data file.

Using the BULK INSERT statement, a format file needs to be created first and then used to provide the format information. To create the format file, use the bcp utility format option:

 C:> bcp "pubs..pub_info" format xxx.dat S servername Usa  P f pub_info.fmt 

The bcp utility prompts for the file storage type, prefix length, field length, and field terminator of each column of pub_info . The values for the logo column are the same as previously. This yields the following format file ( pub_info.fmt ):

 8.0 3 1       SQLCHAR       0       4       ""   1     pub_id   SQL_Latin1_General_         CP1_CI_AS 2       SQLIMAGE      0       5578    ""   2     logo        "" 3       SQLCHAR       4       0       ""   3     pr_info  SQL_Latin1_General_         CP1_CI_AS 

Now the BULK INSERT can be executed:

 BULK INSERT "pubs..pub_info" FROM 'c:\temp\custlogo.doc'  WITH (FORMATFILE = 'c:\pub_info.fmt') 

More information on BULK INSERT will be given later.

NOTE

There is now some sample code (named Bii) that is a standalone console application that mimics the BCP utility that ships with Microsoft SQL Server 2000. Unlike the BCP utility, the bii utility detects when an image field is the destination database field and the input is a filename that can be located in the file system. In this case, instead of loading the filename string in to the image field, the program opens the file, loads the data, and inserts the file contents in the image data type field.