使用字符格式导入或导出数据 (SQL Server)Use character format to import or export data (SQL Server)
09/29/2016
本文内容
适用于：Applies to:
SQL ServerSQL Server(所有支持的版本)
SQL ServerSQL Server (all supported versions)
Azure SQL 数据库Azure SQL Database
Azure SQL 数据库Azure SQL Database
Azure SQL 托管实例Azure SQL Managed Instance
Azure SQL 托管实例Azure SQL Managed Instance
Azure Synapse AnalyticsAzure Synapse Analytics
Azure Synapse AnalyticsAzure Synapse Analytics
并行数据仓库Parallel Data Warehouse
并行数据仓库Parallel Data Warehouse适用于：Applies to:
SQL ServerSQL Server(所有支持的版本)
SQL ServerSQL Server (all supported versions)
Azure SQL 数据库Azure SQL Database
Azure SQL 数据库Azure SQL Database
Azure SQL 托管实例Azure SQL Managed Instance
Azure SQL 托管实例Azure SQL Managed Instance
Azure Synapse AnalyticsAzure Synapse Analytics
Azure Synapse AnalyticsAzure Synapse Analytics
并行数据仓库Parallel Data Warehouse
并行数据仓库Parallel Data Warehouse
将数据批量导出到要在其他程序中使用的文本文件时，或从其他程序生成的文本文件批量导入数据时，建议使用字符格式。Character format is recommended when you bulk export data to a text file that is to be used in another program or when you bulk import data from a text file that is generated by another program.
采用字符格式后，所有列均应用字符数据格式。Character format uses the character data format for all columns. 如果要将数据用于其他程序(如电子表格程序)，或需要通过其他数据库供应商(如 Oracle)将数据复制到 SQL ServerSQL Server 实例中，则以字符格式存储信息会非常有用。Storing information in character format is useful when the data is used with another program, such as a spreadsheet, or when the data needs to be copied into an instance of SQL ServerSQL Server from another database vendor such as Oracle.
备注
当你在 MicrosoftMicrosoftSQL ServerSQL Server 的实例之间批量传输数据，且数据文件包含 Unicode 字符数据，但不包含任何扩展字符或 DBCS 字符时，请使用 Unicode 字符格式。When you bulk transfer data between instances of MicrosoftMicrosoftSQL ServerSQL Server and the data file contains Unicode character data but not any extended or DBCS characters, use the Unicode character format.
使用字符格式的注意事项Considerations for Using Character Format
使用字符格式时，请考虑下列事项：When using character format, consider the following:
默认情况下， bcp 实用工具 使用制表符分隔字符数据字段，并用换行符终止记录。By default, the bcp utility separates the character-data fields with the tab character and terminates the records with the newline character. For information about how to specify alternative terminators, see Specify Field and Row Terminators (SQL Server).
默认情况下，在批量导出或导入字符模式数据之前，将执行下列转换：By default, before the bulk export or import of character-mode data, the following conversions are performed:
批量操作的方向Direction of bulk operation
转换Conversion
导出Export
将数据转换为字符表示形式。Converts data to character representation. 如果进行显式请求，字符列的数据将转换为所请求的代码页。If explicitly requested, the data is converted to the requested code page for character columns. 如果未指定代码页，将通过使用客户端计算机的 OEM 代码页对字符数据进行转换。If no code page is specified, the character data is converted by using the OEM code page of the client computer.
导入Import
必要时将字符数据转换为本机表示形式，并将字符数据从客户端的代码页转换到目标列的代码页。Converts character data to native representation, when necessary, and translates the character data from the client's code page to the code page of the target column(s).
为避免在转换期间丢失扩展字符，请使用 Unicode 字符格式或指定代码页。To prevent loss of extended characters during conversion, either use Unicode character format or specify a code page.
存储在字符格式文件中的所有 sql_variant 数据都是在不包括元数据的情况下进行存储的。Any sql_variant data that is stored in a character-format file is stored without metadata. 每个数据值都将按照隐式数据转换规则转换为 char 格式。Each data value is converted to char format, according to the rules of implicit data conversion. 当数据导入到 sql_variant 列中时，该数据是以 char格式导入的。When imported into a sql_variant column, the data is imported as char. 而导入到数据类型不是 sql_variant的列中时，数据将通过隐式转换从 char 格式转换为其他格式。When imported into a column with a data type other than sql_variant, the data is converted from char by using implicit conversion. 有关数据转换的详细信息，请参阅数据类型转换(数据库引擎)。For more information about data conversion, see Data Type Conversion (Database Engine).
bcp 实用工具 将 money 值作为字符格式数据文件导出时，该数据文件小数点后保留四位数字且不带诸如逗号分隔符之类的任何数字分组符号。The bcp utility exports money values as character-format data files with four digits after the decimal point and without any digit-grouping symbols such as comma separators. 例如，包含值 1,234,567.123456 的 money 列将以字符串 1234567.1235 的形式批量导出到数据文件中。For example, a money column that contains the value 1,234,567.123456 is bulk exported to a data file as the character string 1234567.1235.
字符格式的命令选项Command Options for Character Format
You can import character format data into a table using bcp, BULK INSERT or INSERT ... SELECT * FROM OPENROWSET(BULK...). For a bcp command or BULK INSERT statement, you can specify the data format in the statement. For an INSERT ... SELECT * FROM OPENROWSET(BULK...) statement, you must specify the data format in a format file.
下列命令选项支持字符格式：Character format is supported by the following command options:
CommandCommand
选项Option
说明Description
bcpbcp
-c-c
让 bcp 实用工具使用字符数据。*Causes the bcp utility to use character data.*
BULK INSERTBULK INSERT
DATAFILETYPE ='char'DATAFILETYPE ='char'
在批量导入数据时使用字符格式。Use character format when bulk importing data.
OPENROWSETOPENROWSET
空值N/A
必须使用格式化文件Must use a format file
*若要将字符 ( -c) 数据加载到与旧版 SQL ServerSQL Server 客户端兼容的格式中，请使用 -V 开关。*To load character (-c) data to a format compatible with earlier versions of SQL ServerSQL Server clients, use the -V switch.
备注
或者，您可以在格式化文件中为每个字段指定格式设置。Alternatively, you can specify formatting on a per-field basis in a format file.
示例测试条件Example Test Conditions
本主题中的示例基于下面定义的表和格式化文件。The examples in this topic are based on the table, and format file defined below.
示例表Sample Table
以下脚本将创建测试数据库、名为 myChar 的表并用一些初始值填充表。The script below creates a test database, a table named myChar and populates the table with some initial values. 在 Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS) 中执行以下 Transact-SQL：Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):
CREATE DATABASE TestDatabase;
GO
USE TestDatabase;
CREATE TABLE dbo.myChar (
PersonID smallint NOT NULL,
FirstName varchar(25) NOT NULL,
LastName varchar(30) NOT NULL,
BirthDate date,
AnnualSalary money
);
-- Populate table
INSERT TestDatabase.dbo.myChar
VALUES
(1, 'Anthony', 'Grosse', '1980-02-23', 65000.00),
(2, 'Alica', 'Fatnowna', '1963-11-14', 45000.00),
(3, 'Stella', 'Rossenhain', '1992-03-02', 120000.00);
-- Review Data
SELECT * FROM TestDatabase.dbo.myChar;示例非 XML 格式化文件Sample Non-XML Format File
SQL Server 支持两种类型的格式化文件：非 XML 格式和 XML 格式。SQL Server support two types of format file: non-XML format and XML format. 非 XML 格式是 SQL Server 早期版本支持的原始格式。The non-XML format is the original format that is supported by earlier versions of SQL Server. Please review Non-XML Format Files (SQL Server) for detailed information. 下面的命令基于 的架构使用 bcp 实用工具 myChar.fmt生成非 XML 格式化文件 myChar。The following command will use the bcp utility to generate a non-xml format file, myChar.fmt, based on the schema of myChar. 若要使用 bcp 命令创建格式化文件，请指定 format 参数，并使用 nul 而不是数据文件路径。To use a bcp command to create a format file, specify the format argument and use nul instead of a data-file path. 格式化选项还需要 -f 选项。The format option also requires the -f option. 此外，对于本示例，限定符 c 用于指定字符数据， T 用于指定使用集成安全性的受信任连接。In addition, for this example, the qualifier c is used to specify character data, and T is used to specify a trusted connection using integrated security. 在命令提示符处输入以下命令：At a command prompt, enter the following command:
bcp TestDatabase.dbo.myChar format nul -f D:\BCP\myChar.fmt -T -c
REM Review file
Notepad D:\BCP\myChar.fmt重要
确保非 XML 格式化文件以回车符/换行符结尾。Ensure your non-XML format file ends with a carriage return\line feed. 否则可能会收到以下错误消息：Otherwise you will likely receive the following error message:
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]I/O error while reading BCP format file示例Examples
下面的示例使用上面创建的数据库和格式化文件。The examples below use the database, and format files created above.
使用 bcp 和字符格式导出数据Using bcp and Character Format to Export Data
-c 切换和 OUT 命令。-c switch and OUT command. 请注意：此示例中创建的数据文件将用于所有后续示例中。Note: the data file created in this example will be used in all subsequent examples. 在命令提示符处输入以下命令：At a command prompt, enter the following command:
bcp TestDatabase.dbo.myChar OUT D:\BCP\myChar.bcp -T -c
REM Review results
NOTEPAD D:\BCP\myChar.bcp在不使用格式化文件的情况下使用 bcp 和字符格式导入数据Using bcp and Character Format to Import Data without a Format File
-c 切换和 IN 命令。-c switch and IN command. 在命令提示符处输入以下命令：At a command prompt, enter the following command:
REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.myChar;"
REM Import data
bcp TestDatabase.dbo.myChar IN D:\BCP\myChar.bcp -T -c
REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.myChar;"在使用非 XML 格式化文件的情况下使用 bcp 和字符格式导入数据Using bcp and Character Format to Import Data with a Non-XML Format File
-c 和 -f 切换以及 IN 命令。-c and -f switches and IN command. 在命令提示符处输入以下命令：At a command prompt, enter the following command:
REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.myChar;"
REM Import data
bcp TestDatabase.dbo.myChar IN D:\BCP\myChar.bcp -f D:\BCP\myChar.fmt -T
REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.myChar;"在不使用格式化文件的情况下使用 BULK INSERT 和字符格式Using BULK INSERT and Character Format without a Format File
DATAFILETYPE 参数。DATAFILETYPE argument. 在 Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS) 中执行以下 Transact-SQL：Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):
TRUNCATE TABLE TestDatabase.dbo.myChar; -- for testing
BULK INSERT TestDatabase.dbo.myChar
FROM 'D:\BCP\myChar.bcp'
WITH (
DATAFILETYPE = 'Char'
);
-- review results
SELECT * FROM TestDatabase.dbo.myChar;在使用非 XML 格式化文件的情况下使用 BULK INSERT 和字符格式Using BULK INSERT and Character Format with a Non-XML Format File
FORMATFILE 参数。FORMATFILE argument. 在 Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS) 中执行以下 Transact-SQL：Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):
TRUNCATE TABLE TestDatabase.dbo.myChar; -- for testing
BULK INSERT TestDatabase.dbo.myChar
FROM 'D:\BCP\myChar.bcp'
WITH (
FORMATFILE = 'D:\BCP\myChar.fmt'
);
-- review results
SELECT * FROM TestDatabase.dbo.myChar;在使用非 XML 格式化文件的情况下使用 OPENROWSET 和字符格式Using OPENROWSET and Character Format with a Non-XML Format File
FORMATFILE 参数。FORMATFILE argument. 在 Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS) 中执行以下 Transact-SQL：Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):
TRUNCATE TABLE TestDatabase.dbo.myChar; -- for testing
INSERT INTO TestDatabase.dbo.myChar
SELECT *
FROM OPENROWSET (
BULK 'D:\BCP\myChar.bcp',
FORMATFILE = 'D:\BCP\myChar.fmt'
) AS t1;
-- review results
SELECT * FROM TestDatabase.dbo.myChar;相关任务Related Tasks
使用数据格式进行批量导入或批量导出To use data formats for bulk import or bulk export
另请参阅See Also
