The bcp 9.0 client is installed when you install Microsoft SQL Server tools on your system. If both SQL Server 2005 and SQL Server 2000 tools are installed, depending on the value of the PATH environment variable, you might be using the earlier bcp client instead of the bcp 9.0 client. This environment variable defines the set of directories used by Windows to search for executable files. To discover which version you are using, run the bcp /v command at the Windows Command Prompt. For information about how to set the command path in the PATH environment variable, see Windows Help.
XML format files are only supported when SQL Server tools are installed together with SQL Native Client.
For information about where to find or how to run the bcp utility and about the command prompt utilities syntax conventions, see Command Prompt Utilities.
For information on preparing data for bulk import or export operations, see Preparing Data for Bulk Export or Import.
For information about when row-insert operations that are performed by bulk import are logged in the transaction log, see Prerequisites for Minimal Logging in Bulk Import.
Computed Columns and timestamp Columns
Values in the data file being imported for computed or timestamp columns are ignored, and SQL Server 2005 automatically assigns values. If the data file does not contain values for the computed or timestamp columns in the table, use a format file to specify that the computed or timestamp columns in the table should be skipped when importing data; SQL Server automatically assigns values for the column.
Computed and timestamp columns are bulk copied from SQL Server to a data file as usual.
Specifying Identifiers that Contain Spaces or Quotation Marks
SQL Server identifiers can include characters such as embedded spaces and quotation marks. Such identifiers must be treated as follows:
-
When you specify an identifier or file name that includes a space or quotation mark at the command prompt, enclose the identifier in quotation marks ("").
For example, the following bcp out command creates a data file named Currency Types.dat:
bcp AdventureWorks.Sales.Currency out "Currency Types.dat" -T -c
-
To specify a database name that contains a space or quotation mark, you must use the q option.
-
For owner, table, or view names that contain embedded spaces or quotation marks, you can either:
-
Specify the -q option, or
-
Enclose the owner, table, or view name in brackets ([]) inside the quotation marks.
Data Validation
In SQL Server 2005, bcp enforces new data validation and data checks that might cause existing scripts to fail when they are executed on invalid data in a data file. For example, bcp now verifies that:
-
The native representation of float or real data types are valid.
-
Unicode data has an even-byte length.
Forms of invalid data that could be bulk imported in earlier versions of SQL Server might fail to load now; whereas, in earlier versions, the failure did not occur until a client tried to access the invalid data. The added validation minimizes surprises when querying the data after bulk load.
Bulk Exporting or Importing SQLXML Documents
To bulk export or import SQLXML data, use one of the following data types in your format file.
|
Data type
|
Effect
|
|---|
|
SQLCHAR or SQLVARYCHAR
|
The data is sent in the client code page or in the code page implied by the collation). The effect is the same as specifying the -c switch without specifying a format file.
|
|
SQLNCHAR or SQLNVARCHAR
|
The data is sent as Unicode. The effect is the same as specifying the -w switch without specifying a format file.
|
|
SQLBINARY or SQLVARYBIN
|
The data is sent without any conversion.
|
This section contains the following examples:
-
A. Copying table rows into a data file (with a trusted connection)
-
B. Copying table rows into a data file (with Mixed-mode Authentication)
-
C. Copying data from a file to a table
-
D. Copying a specific column into a data file
-
E. Copying a specific row into a data file
-
F. Copying data from a query to a data file
-
G. Creating a non-XML format file
-
H. Creating an XML format file
-
I. Using a format file to bulk import with bcp
A. Copying table rows into a data file (with a trusted connection)
The following example illustrates the out option on the AdventureWorks.Sales.Currency table. This example creates a data file named Currency.dat and copies the table data into it using character format. The example assumes that you are using Windows Authentication and have a trusted connection to the server instance on which you are running the bcp command.
At a command prompt, enter the following command:
bcp AdventureWorks.Sales.Currency out Currency.dat -T -c
B. Copying table rows into a data file (with mixed-mode authentication)
The following example illustrates the out option on the AdventureWorks.Sales.Currency table. This example creates a data file named Currency.dat and copies the table data into it using character format.
The example assumes that you are using mixed-mode authentication, you must use the -U switch to specify your login ID. Also, unless you are connecting to the default instance of SQL Server on the local computer, use the -S switch to specify the system name and, optionally, an instance name.
bcp AdventureWorks.Sales.Currency out Currency.dat -c -U<login_id> -S<server_name\instance_name>
The system will prompt you for your password.
C. Copying data from a file to a table
The following example illustrates the in option by using the file created in the preceding example (Currency.dat). First, however, this example creates an empty copy of the AdventureWorks Sales.Currency table, Sales.Currency2, into which the data is copied. The example assumes that you are using Windows Authentication and have a trusted connection to the server instance on which you are running the bcp command.
To create the empty table, in Query Editor, enter the following command:
USE AdventureWorks;
GO
SELECT * INTO AdventureWorks.Sales.Currency2
FROM AdventureWorks.Sales.Currency WHERE 1=2
To bulk copy the character data into the new table, that is to import the data, enter the following command at a command prompt:
bcp AdventureWorks.Sales.Currency2 in Currency.dat -T -c
To verify that the command succeeded, display the contents of the table in Query Editor, and enter:
USE AdventureWorks;
GO
SELECT * FROM Sales.Currency2
D. Copying a specific column into a data file
To copy a specific column, you can use the queryout option. The following example copies only the Name column of the Sales.Currency table into a data file. The example assumes that you are using Windows Authentication and have a trusted connection to the server instance on which you are running the bcp command.
At the Windows command prompt, enter:
bcp "SELECT Name FROM AdventureWorks.Sales.Currency" queryout Currency.Name.dat -T -c
E. Copying a specific row into a data file
To copy a specific row, you can use the queryout option. The following example copies only the row for the contact named Jarrod Rana from the AdventureWorks.Person.Contact table into a data file (Jarrod Rana.dat). The example assumes that you are using Windows Authentication and have a trusted connection to the server instance on which you are running the bcp command.
At the Windows command prompt, enter:
bcp "SELECT * FROM AdventureWorks.Person.Contact WHERE FirstName='Jarrod' AND LastName='Rana' " queryout "Jarrod Rana.dat" -T -c
F. Copying data from a query to a data file
To copy the result set from a Transact-SQL statement to a data file, use the queryout option. The following example copies the names from the AdventureWorks.Person.Contact table, ordered by last name then first name, into the Contacts.txt data file. The example assumes that you are using Windows Authentication and have a trusted connection to the server instance on which you are running the bcp command.
At the Windows command prompt, enter:
bcp "SELECT FirstName, LastName FROM AdventureWorks.Person.Contact ORDER BY LastName, Firstname" queryout Contacts.txt -c -T
G. Creating a non-XML format file
The following example creates a non-XML format file, Currency.fmt, for the Sales.Currency table in the AdventureWorks database. The example assumes that you are using Windows Authentication and have a trusted connection to the server instance on which you are running the bcp command.
At the Windows command prompt, enter:
bcp AdventureWorks.Sales.Currency format nul -T -c -f Currency.fmt
For more information, see Understanding Non-XML Format Files.
H. Creating an XML format file
The following example creates an XML format file named Currency.xml for the Sales.Currency table in the AdventureWorks database. The example assumes that you are using Windows Authentication and have a trusted connection to the server instance on which you are running the bcp command.
At the Windows command prompt, enter:
bcp AdventureWorks.Sales.Currency format nul -T -c -x -f Currency.xml
.gif) Note: |
|---|
|
To use the -x switch, you must be using a bcp 9.0 client. For information about how to use the bcp 9.0 client, see "Remarks."
|
For more information, see Understanding XML Format Files.
I. Using a format file to bulk import with bcp
To use a previously created format file when importing data into an instance of SQL Server, use the -f switch with the in option. For example, the following command bulk copies the contents of a data file, Currency.dat, into a copy of the Sales.Currency table (Sales.Currency2) by using the previously created format file (Currency.xml). The example assumes that you are using Windows Authentication and have a trusted connection to the server instance on which you are running the bcp command.
At the Windows command prompt, enter:
bcp AdventureWorks.Sales.Currency2 in Currency.dat -T -f Currency.xml
| Note: |
|---|
|
Format files are useful when the data file fields are different from the table columns; for example, in their number, ordering, or data types. For more information, see Format Files for Importing or Exporting Data.
|