Bulk Copy Program
3 4
Using BCP, you can copy data from a data file into SQL Server or you can copy data from SQL Server into a data file. BCP is useful for transferring data into SQL Server from other databases as well as for transferring user-generated data. In this section, you'll learn how to use BCP and its options and how to format data so that you can copy it into and out of SQL Server by using BCP.
BCP Syntax
BCP is a command-line executable program that is invoked from the command prompt window. BCP requires certain parameters and offers many optional parameters for you to use. The format of the BCP command is shown here. (All the required and optional parameters are shown.)
bcp {[[database_name.][owner].]{table_name | view_name} | "query"} {in | out | queryout | format} data_file [-m max_errors] [-f format_file] [-e error_file] [-F first_row] [-L last_row] [-b batch_size] [-n] [-c] [-w] [-N] [-V (60 | 65 | 70)] [-6] [-q] [-C code_page] [-t field_term] [-r row_term] [-i input_file] [-o output_file] [-a packet_size] [-S server_name[\instance_name]] [-U login_id] [-P password] [-T] [-v] [-R] [-k] [-E] [-h "hint [,…n]"] Required Parameters
The required parameters specify the data extraction and insertion locations, among other things. As mentioned, using BCP, you can extract data from a data file and insert it into a SQL Server table (or view), or you can extract data from a table (or view) and insert it into a data file.
You specify the table or view used in the bulk copy operation in one of two ways. First, you can use the table/view_definition parameter. The simplest definition consists of the table or view name. As shown in the preceding command, you have the option of specifying the name of the database where the specified table or view resides, the owner of the table or view, or both. If you do not specify a database name, this is the default database specified in the user's login definition. See Chapter 34 for more information about user definitions.
Alternatively, you can specify the table or view by using a query. When you use this method, you are specifying which data will be extracted from the table or view. (The table/view_definition parameter can be used to specify the table or view used for data extraction or insertion.) This query must be enclosed in double quotation marks and can consist of a SELECT statement with or without clauses such as ORDER BY. If you specify a query definition, you must also specify the queryout parameter (discussed in Table 24-1).
The location of the data file used in the bulk copy operation is specified by the data_file parameter. This must be a valid path.
Finally, you must specify one or more of the parameters listed in Table 24-1.
Table 24-1. The bulk copy directional specifiers
| Parameter | Description |
|---|---|
| In | Specifies that the bulk copy operation will copy data from the data file into the table or view in the SQL Server database. |
| Out | Specifies that the bulk copy operation will extract data from the SQL Server table or view and insert it into the data file. |
| Queryout | Specifies that data will be extracted from the SQL Server database by means of the defined query. The bulk copy will then copy the data that the query selects into the data file. |
| Format | Specifies that BCP will create a format file in addition to performing the bulk copy operation. The formatting options (- n, - c, - w, - 6, or - N) and the table or view delimiters are used to create the format file. The format parameter must be accompanied by the - f option. The format file allows you to store the BCP definitions so that you do not need to repeat them when you subsequently use BCP. |
Optional Parameters
You can use the optional parameters listed in Table 24-2 to modify the way in which BCP performs bulk copies.
Table 24-2. The optional bulk copy directional specifiers
| Parameter | Description |
|---|---|
| -a packet_size | Specifies the number of bytes per network packet sent between the client and the server. |
| -b batch_size | Specifies the number of rows to be included in a batch. Each batch is copied as one transaction. By default, all of the rows in the data file are copied as one batch, using one commit. You might want to specify this option when you perform bulk inserts so that table locks are released as batches are processed, thus allowing other processing. |
| -c | Specifies that BCP use a character data type. |
| -e err_file | Specifies the path of an error file in which BCP errors are logged. |
| -f format_file | Specifies the path of a format file that BCP has used previously. A format file is created if BCP is run with the format option specified, as described earlier. If the format file is used, other formatting options need not be included. |
| -h "hint [,…n]" | Specifies hints to use during the bulk copy. These hints can be any of the following:
|
| -i input_file | Specifies the name of the response file. The response file contains responses to the questions asked by BCP when the database is running in interactive mode. |
| -k | Specifies that empty columns get null values rather than default values. |
| -m max_errors | Specifies how many errors can occur before BCP terminates. If this option is not included, the default value is 10. |
| -n | Specifies that BCP use native data types. |
| -o output_file | Specifies the output file that receives BCP output. This output file is a normal text file that you can read by using Microsoft Notepad or other utilities. |
| -q | Specifies that quoted identifiers be required for table and view names that contain non-ANSI characters such as spaces. |
| -r row_term | Specifies the row terminator. The default is the newline character. |
| -t field_term | Specifies the field terminator, also known as the delimiter. The default is the tab character. |
| -v | Prints the version number of and copyright information about BCP. |
| -w | Specifies that BCP use Unicode characters. |
| -C code_page | Specifies the code page of the data in the data file. |
| -E | Specifies that the file being copied contains values for identity columns. |
| -F first_row | Specifies the first row to start the bulk copy. If no row is specified, the first row will be row 1. This option is useful if you want to skip header information in the data file. |
| -L last_row | Specifies the last row to perform the bulk copy. The default value of 0 specifies that the last row to be copied will be the last row in the data file. This option is useful if you want to copy only a certain number of rows. |
| -N | Specifies that BCP use native data types for noncharacter data and Unicode for character data. |
| -P password | Specifies the password for the login ID used in the -U option. |
| -R | Specifies that currency, date, and time data use the regional format of the client system. |
| -S server_name | Specifies the name of the server to copy into. |
| -T | Specifies that a trusted connection be used. The login_id and password variables are not needed if this option is used; the network user credentials are used. |
| -U login_id | Specifies the user login ID to copy the data under. |
| -V 60 | 65 | 70 | Performs the bulk copy using data types from an earlier version of SQL Server. This option should be used with the -c and -n options. |
| -6 | Specifies that BCP use Microsoft SQL Server 6 or Microsoft SQL Server 6.5 data types. |
As you can see, numerous options and combinations of options can be used to take advantage of BCP. The best way to begin learning how these options can be used is to work through examples of using BCP, which we'll do next.
Using BCP
In this section, we'll look at several examples of using BCP to load data into and out of SQL Server. These examples don't cover every possibility, but they should give you a good idea of the variety of modes and methods of operation available with BCP.
You can use BCP from the command-line as described earlier, or you can use BCP in a more interactive fashion. To invoke BCP without having any additional interaction with the program, you must specify the -n, -c, -w, or -N parameter. If none of these parameters is specified, BCP will operate in the interactive mode.
NOTE
All of the following examples use the Customers table from the Northwind database.
Loading Data Using BCP Interactively
Using BCP in interactive mode to load data is somewhat difficult because this technique requires you to specify column lengths and types. You do not have to do this when you use command-line options, as described in the next section. Although using BCP in interactive mode to load data is not recommended, we'll look at an example of this technique so that you will have a thorough understanding of how BCP works. In this example, we'll copy data from the file data2.file into the Customers table of the Northwind database, writing errors to the file err.file. You must have previously created the data file data2.file. This file contains the data you want to load into the Customers table. To start the interactive session as the system administrator, enter the following command:
bcp Northwind.dbo.Customers in data2.file -e err.file -Usa
You will then be prompted for a password. Enter the system administrator (sa) password.
Next BCP will require you to provide information regarding the data you want to copy. A sample BCP interactive session follows. Note that user input appears in bold type.
Enter the file storage type of field CustomerID [nchar]: char Enter prefix-length of field CustomerID [1]: 0 Enter length of field CustomerID [26]: 5 Enter field terminator [none]: , Enter the file storage type of field CompanyName [nvarchar]: char Enter prefix-length of field CompanyName [1]: 0 Enter length of field CompanyName [189]: 40 Enter field terminator [none]: , Enter the file storage type of field ContactName [nvarchar]: char Enter prefix-length of field ContactName [1]: 0 Enter length of field ContactName [143]: 30 Enter field terminator [none]: , Enter the file storage type of field ContactTitle [nvarchar]: char Enter prefix-length of field ContactTitle [1]: 0 Enter length of field ContactTitle [143]: 30 Enter field terminator [none]: , Enter the file storage type of field Address [nvarchar]: char Enter prefix-length of field Address [1]: 0 Enter length of field Address [283]: 60 Enter field terminator [none]: , Enter the file storage type of field City [nvarchar]: char Enter prefix-length of field City [1]: 0 Enter length of field City [73]: 15 Enter field terminator [none]: , Enter the file storage type of field Region [nvarchar]: char Enter prefix-length of field Region [1]: 0 Enter length of field Region [73]: 15 Enter field terminator [none]: , Enter the file storage type of field PostalCode [nvarchar]: char Enter prefix-length of field PostalCode [1]: 0 Enter length of field PostalCode [49]: 10 Enter field terminator [none]: , Enter the file storage type of field Country [nvarchar]: char Enter prefix-length of field Country [1]: 0 Enter length of field Country [73]: 15 Enter field terminator [none]: , Enter the file storage type of field Phone [nvarchar]: char Enter prefix-length of field Phone [1]: 0 Enter length of field Phone [115]: 24 Enter field terminator [none]: , Enter the file storage type of field Fax [nvarchar]: char Enter prefix-length of field Fax [1]: 0 Enter length of field Fax [115]: 24 Enter field terminator [none]: , Do you want to save this format information in a file? [Y/n]: Y Host filename [bcp.fmt]: data.fmt Starting copy... SQLState = S1000, NativeError = 0 Error = [Microsoft][ODBC SQL Server Driver] Unexpected EOF encountered in BCP data-file 5 rows copied. Network packet size (bytes): 4096 Clock Time (ms.): total 51 Avg 10 (98.04 rows per sec.)
As you can see, you must know many of these values before you begin. An error message was generated when BCP reached the end of the file because we did not specify the number of rows to be copied.
Loading Data Using BCP with Command-Line Options
As mentioned, you will find it much easier to use BCP to load data when you use the command-line options. In the example in this section, we'll use BCP to load data from a data file that is made up of tab-delimited character columns. We'll use the -c option to specify that the data is in character format in the data file. The use of the -c option also makes BCP run in noninteractive mode. The following command copies 25 lines of data from the file data.file into the Customers table in the Northwind database:
bcp Northwind.dbo.Customers in data.file -e err.fil -c -Usa
Because the -c option specifies character data, you do not have to provide the field length and prefix length. Assuming that you have created data.file with 25 rows and tab-delimited character data corresponding to the columns in the Customers table, and that you have entered the sa password, the session should look something like this. (Your network packet size and clock time numbers might differ.)
Starting copy... 25 rows copied. Network packet size (bytes): 4096 Clock Time (ms.): total 80 Avg 3 (312.50 rows per sec.)
In this example, the direction of the copy was set to in to indicate that data be transferred into the database. It's always a good idea to specify an error file because it provides you with a log of any error encountered during the BCP session. If you do not specify an error file, the errors will be sent to your screen and will eventually scroll out of view.
Loading Data Using the format Option
In our first example in the "Using BCP" section, we created a format file named data.fmt. Rather than entering all of the formatting options such as storage type, prefix length, field length, and field terminator by hand, you can use this format file. You invoke this file by using the -f (format) option, as shown here:
bcp Northwind.dbo.Customers in data2.file -e err.fil -f data.fmt -L 5 -Usa
Assuming that you have entered the sa password and created data2.file, the session should look something like this:
Starting copy... 5 rows copied. Network packet size (bytes): 4096 Clock Time (ms.): total 50 Avg 10 (100.00 rows per sec.)
In addition to including the -f option, this example includes the -L option. The -L option indicates the last row to copy from the input file. In this example, the fifth row was the last row to be processed. This specification eliminates the EOF error message seen in the earlier example.
Extracting Data Using BCP Interactively
Using BCP in interactive mode to copy data out of a database is easier than using it interactively to copy data into a database because when you extract data, BCP will fill in the field-length options for you. Let's use the following command to interactively copy data out of the Customers table in the Northwind database:
bcp Northwind.dbo.Customers out dataout.dat -e err.fil -U sa
After you enter this command and the sa password, the session will proceed. A sample session follows. (User input is shown in bold type.)
Enter the file storage type of field CustomerID [nchar]: char Enter prefix-length of field CustomerID [1]: 0 Enter length of field CustomerID [26]: Enter field terminator [none]: , Enter the file storage type of field CompanyName [nvarchar]: char Enter prefix-length of field CompanyName [1]: 0 Enter length of field CompanyName [189]: Enter field terminator [none]: , Enter the file storage type of field ContactName [nvarchar]: char Enter prefix-length of field ContactName [1]: 0 Enter length of field ContactName [143]: Enter field terminator [none]: , Enter the file storage type of field ContactTitle [nvarchar]: char Enter prefix-length of field ContactTitle [1]: 0 Enter length of field ContactTitle [143]: Enter field terminator [none]: , Enter the file storage type of field Address [nvarchar]: char Enter prefix-length of field Address [1]: 0 Enter length of field Address [283]: Enter field terminator [none]: , Enter the file storage type of field City [nvarchar]: char Enter prefix-length of field City [1]: 0 Enter length of field City [73]: Enter field terminator [none]: , Enter the file storage type of field Region [nvarchar]: char Enter prefix-length of field Region [1]: 0 Enter length of field Region [73]: Enter field terminator [none]: , Enter the file storage type of field PostalCode [nvarchar]: char Enter prefix-length of field PostalCode [1]: 0 Enter length of field PostalCode [49]: Enter field terminator [none]: , Enter the file storage type of field Country [nvarchar]: char Enter prefix-length of field Country [1]: 0 Enter length of field Country [73]: Enter field terminator [none]: , Enter the file storage type of field Phone [nvarchar]: char Enter prefix-length of field Phone [1]: 0 Enter length of field Phone [115]: Enter field terminator [none]: , Enter the file storage type of field Fax [nvarchar]: char Enter prefix-length of field Fax [1]: 0 Enter length of field Fax [115]: Enter field terminator [none]: , Do you want to save this format information in a file? [Y/n]: n Host filename [bcp.fmt]: Starting copy... 96 rows copied. Network packet size (bytes): 4096 Clock Time (ms.): total 10 Avg 0 (9600.00 rows per sec.)
This interactive BCP session creates a tab-delimited file (remember that tab delimiters are the default delimiter) that you can view using Notepad. This file is a text file, and all of the data is character data. Unfortunately, BCP does not add a newline character at the end of each line. Thus, if you do view the file by using Notepad, you will see one extremely long line of data.
Extracting Data Using BCP with Command-Line Options
In order to create a more readable data file that is tab-delimited and ends each line with a newline character, use the -c option, as shown here:
bcp Northwind.dbo.Customers out dataout.dat -e err.fil -c -U sa
After you enter this command and the sa password, the session will proceed, as follows:
Starting copy... 96 rows copied. Network packet size (bytes): 4096 Clock Time (ms.): total 1 Avg 0 (96000.00 rows per sec.)
Extracting Data Using the queryout Option
Our final example uses the queryout option to extract data. This option enables you to specify a query when copying data out of the SQL Server database. This query selects certain data, and only that data will be copied. The queryout option is fairly easy to use—just remember to enclose your query in double quotation marks, as shown here:
bcp "SELECT CustomerID, CompanyName FROM Northwind..Customers" queryout dataout.dat -e err.fil -c -U sa
As usual, enter the sa password; the session will appear as follows:
Starting copy... 96 rows copied. Network packet size (bytes): 4096 Clock Time (ms.): total 1 Avg 0 (96000.00 rows per sec.)
The output from this query is a tab-delimited, row-terminated data file consisting of two columns: CustomerID and CompanyName. This technique is useful when you want to extract only certain database columns or rows.
EAN: N/A
Pages: 264