Department table in the AdventureWorks sample database. The HumanResources. The following example creates an XML format file, Department-n. Department table. The format file uses native data types. The contents of the generated format file are presented after the command. The following example creates an XML format file, Department.
The format file uses character data formats and a non-default field terminator ,. Department table, use the following command:. Department table that uses default terminators, use the following command:. The following example format file for a table with 5 columns includes the collation.
Therefore, if you generate a format file, you must manually delete the collation info from the generated format file before you start importing data back into SQL Server. The following is an example of the format file without the collation info. The format option always requires the -f option, and to create an XML format file, you must also specify the -x option, such as:. To distinguish an XML format file, we recommend that you use. This section contains the following examples that show how to use bcp commands to create an XML format file:.
Adventure Works Cycles is a fictional manufacturing company used to demonstrate database concepts and scenarios. Feedback will be sent to Microsoft: By pressing the submit button, your feedback will be used to improve Microsoft products and services. Privacy policy.
When you bulk import data into a SQL Server table or bulk export data from a table, you can use a format file to store all the format information that is required to bulk export or bulk import data. This includes format information for each field in a data file relative to that table. Both non-XML format files and XML format files contain descriptions of every field in a data file, and XML format files also contain descriptions of the corresponding table columns.
However, we recommend that you use the XML syntax for new format files because they provide several advantages over non-XML format files. These format files correspond to the HumanResources. 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. This browser is no longer supported. Download Microsoft Edge More info. Contents Exit focus mode. Is this page helpful? Please rate your experience Yes No. Any additional feedback? Note If you use bcp to back up your data, create a format file to record the data format.
Note Using a format file in with the in or out option is optional. Note If the target table is clustered columnstore index, TABLOCK hint is not required for loading by multiple concurrent clients because each concurrent thread is assigned a separate rowgroup within the index and loads data into it.
Note bcp now enforces data validation and data checks that might cause scripts to fail if they're executed on invalid data in a data file. Note The -m option also does not apply to converting the money or bigint data types. Important When the bcp utility is connecting to SQL Server with a trusted connection using integrated security, use the -T option trusted connection instead of the user name and password combination.
Note Disabling constraints is the default behavior. Note By default, triggers are not fired. Note To use the -x switch, you must be using a bcp 9. 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. Submit and view feedback for This product This page. View all page feedback. In this article. Default code page used by the client. This is the default code page used if -C is not specified.
No conversion from one code page to another occurs. This is the fastest option because no conversion occurs. Specific code page number; for example, Versions prior to version 13 SQL Server 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.
The data is sent as Unicode.
0コメント