The version of the bcp utility Bcp. For example, SQL Server This topic describes how to use the bcp utility to create a format file for a particular table. The format file is based on the data-type option specified -n , -c , -w ,or -N and the table or view delimiters. To use a bcp command to create a format file, specify the format argument and use nul instead of a data-file path. The format option also requires the -f option, such as:. To distinguish a non-XML format file, we recommend that you use.
This section contains the following examples that show how to use bcp commands to create a non-XML format file:. The examples use the HumanResources. Department table in the AdventureWorks sample database.
The HumanResources. The following example creates an XML format file, Department-n. Department table. To make sure the newest version of the bcp utility is running you need to remove any older versions of the bcp utility.
For information about where to find or how to run the bcp utility and about the command prompt utilities syntax conventions, see Command Prompt Utility Reference Database Engine. 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. Using additional special characters. If you use quotation marks to enclose a string that contains one of the special characters, the quotation marks are set as part of the environment variable value.
Values in the data file being imported for computed or timestamp columns are ignored, and SQL Server 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.
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. To specify a database name that contains a space or quotation mark, you must use the -q option.
For example, bcp now verifies that:. 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 bulkload. Disabling constraints is the default behavior. By default, triggers are not fired. This new requirement might cause bcp scripts that do not enforce triggers and constraint checks to fail if the user account lacks ALTER table permissions for the target table. Use the native format to export and import using SQL Server. Consider overriding the default terminators using -t and -r options with random hexadecimal values to avoid conflicts between terminator values and data values.
User Use a long and unique terminator any sequence of bytes or characters to minimize the possibility of a conflict with the actual string value. This can be done by using the -t and -r options.
Except where specified otherwise, the examples assume that you are using Windows Authentication and have a trusted connection to the server instance on which you are running the bcp command. The script below creates an empty copy of the WideWorldImporters.
StockItemTransactions table and then adds a primary key constraint. The following examples illustrate the out option on the WideWorldImporters. StockItemTransactions table.
The example also: specifies the maximum number of syntax errors, an error file, and an output file. The following example illustrates the out option on the WideWorldImporters. 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. At a command prompt, enter the following command: The system will prompt you for your password. The following examples illustrate the in option on the WideWorldImporters. The example also: use the hint TABLOCK , specifies the batch size, the maximum number of syntax errors, an error file, and an output file.
To copy a specific column, you can use the queryout option. StockItemTransactions table into a data file. To copy a specific row, you can use the queryout option. The following example copies only the row for the person named Amy Trefl from the WideWorldImporters. Note: the -d switch is used identify the database. 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 WideWorldImporters.
People table, ordered by full name, into the People. Note: the -t switch is used to create a comma-delimited file. The following example creates three different format files for the Warehouse.
Review the contents of each created file. To use the -x switch, you must be using a bcp 9. For information about how to use the bcp 9. To use a previously created format file when importing data into an instance of SQL Server, use the -f switch with the in option.
Note: the -L switch is used to import only the first records. Format files are useful when the data file fields are different from the table columns; for example, in their number, ordering, or data types.
Skip to main content. For example,. IN: This option is used to import all the records to an existing table. This requires the table to be created before executing the BCP command. Using queryout the SQL statement can be run on a defined connection and the data can be dumped into another file; the other switches are the same in this example as well. The BCP utility supports the use of a format file that contains the formatting details of each field in a file.
The format file is used to provide all the required formatting details for bulk export and bulk import operations. In this section we shall walk through some of the concepts of the format file, look at how to create the format file, and other details. We also need to design the input parameters to generate a desired non-XML format file and this is actually simple and straight-forward.
Note those 'xyz' strings are just a place holder for columns that do not have corresponding source fields. Actually, you can put any string there. This tip provides a quick way to generate a customized BCP format file when you need to extract a few source fields into a few columns of a target table. It currently only deals with ASCII type source file, which is arguably the most common type, not the native format source file. You can read the following articles to better understand how to use bulk insert methods in a SQL Server environment:.
0コメント