使用本机格式导入或导出数据 (SQL Server)Use Native Format to Import or Export Data (SQL Server)

适用对象:是SQL Server 是Azure SQL 数据库 是Azure Synapse Analytics (SQL DW) 是并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

当使用不包含任何扩展/双字节字符集 (DBCS) 字符的数据文件在多个 SQL ServerSQL Server 实例之间大容量传输数据时,建议使用本机格式。Native format is recommended when you bulk transfer data between multiple instances of SQL ServerSQL Server using a data file that does not contain any extended/double-byte character set (DBCS) characters.

备注

若要使用包含扩展字符或 DBCS 字符的数据文件在多个 SQL ServerSQL Server 实例之间大容量传输数据,应使用 Unicode 本机格式。To bulk transfer data between multiple instances of SQL ServerSQL Server by using a data file that contains extended or DBCS characters, you should use the Unicode native format. 有关详细信息信息,请参阅 使用 Unicode 本机格式导入或导出数据 (SQL Server)For more information, see Use Unicode Native Format to Import or Export Data (SQL Server).

本机格式保留数据库的本机数据类型。Native format maintains the native data types of a database. 本机格式适用于 SQL ServerSQL Server 表之间的高速数据传输。Native format is intended for high-speed data transfer of data between SQL ServerSQL Server tables. 如果使用格式化文件,则源表和目标表不必相同。If you use a format file, the source and target tables do not need to be identical. 数据传输分为两个步骤:The data transfer involves two steps:

  1. 将源表中的数据批量导出到数据文件中Bulk exporting the data from a source table into a data file

  2. 将数据文件中的数据批量导入到目标表中Bulk importing the data from the data file into the target table

在相同的表之间使用本机格式避免了在各数据类型与字符格式之间进行不必要的转换,从而节省了时间和空间。The use of native format between identical tables avoids unnecessary conversion of data types to and from character format, saving time and space. 但是,若要获得最佳的传输速率,应执行几个有关数据格式的检查。To achieve the optimum transfer rate, however, few checks are performed regarding data formatting. 为了防止加载的数据出现问题,请参阅以下限制列表。To prevent problems with the loaded data, see the following restrictions list.

本主题内容:In this Topic:
限制Restrictions
bcp 如何处理本机格式的数据How bcp Handles Data in Native Format
本机格式的命令选项Command Options for Native Format
示例测试条件Example Test Conditions

 ● 示例表 ● Sample Table
 ● 示例非 XML 格式化文件 ● Sample Non-XML Format File
示例Examples
 ● 使用 bcp 和本机格式导出数据 ● Using bcp and Native Format to Export Data
 ● 在不使用格式化文件的情况下使用 bcp 和本机格式导入数据 ● Using bcp and Native Format to Import Data without a Format File
 ● 在使用非 XML 格式化文件的情况下使用 bcp 和本机格式导入数据 ● Using bcp and Native Format to Import Data with a Non-XML Format File
 ● 在不使用格式化文件的情况下使用 BULK INSERT 和本机格式 ● Using BULK INSERT and Native Format without a Format File
 ● 在使用非 XML 格式化文件的情况下使用 BULK INSERT 和本机格式 ● Using BULK INSERT and Native Format with a Non-XML Format File
 ● 在使用非 XML 格式化文件的情况下使用 OPENROWSET 和本机格式 ● Using OPENROWSET and Native Format with a Non-XML Format File
相关任务Related Tasks

限制Restrictions

若要成功导入本机格式的数据,请确保:To import data in native format successfully, ensure that:

  • 数据文件是本机格式的文件。The data file is in native format.

  • 目标表必须与数据文件(含有正确的列数、数据类型、长度及 NULL 状态等)兼容,或者您必须使用格式化文件将每一个字段映射到其相应列。Either the target table must be compatible with the data file (having the correct number of columns, data type, length, NULL status, and so forth), or you must use a format file to map each field to its corresponding columns.

    备注

    如果从与目标表不匹配的文件中导入数据,那么导入操作可能会成功,但插入到目标表中的数据值很可能是错误的。If you import data from a file that is mismatched with the target table, the import operation might succeed but the data values inserted into the target table are likely to be incorrect. 这是由于文件中的数据是通过使用目标表的格式来解释的。This is because the data from the file is interpreted by using the format of the target table. 因此,任何不匹配都会导致插入错误值。Therefore, any mismatch results in the insertion of incorrect values. 但是,这样的不匹配决不会导致数据库中出现逻辑或物理不一致。However, under no circumstances can such a mismatch cause logical or physical inconsistencies in the database.

    有关使用格式化文件的信息,请参阅用于导入或导出数据的格式化文件 (SQL Server)For information on using format files, see Format Files for Importing or Exporting Data (SQL Server).

成功的导入操作不会损坏目标表。A successful import will not corrupt the target table.

bcp 如何处理本机格式的数据How bcp Handles Data in Native Format

本节论述了 bcp 实用工具如何导出和导入本机格式数据的特殊注意事项。This section discusses special considerations for how the bcp utility exports and imports data in native format.

  • 非字符数据Noncharacter data

    bcp 实用工具 使用 SQL ServerSQL Server 内部二进制数据格式将表中的非字符数据写入数据文件中。The bcp utility uses the SQL ServerSQL Server internal binary data format to write noncharacter data from a table to a data file.

  • char 数据或 varchar 数据char or varchar data

    在每个 char 字段或 varchar 字段的开头, bcp 都添加前缀长度。At the beginning of each char or varchar field, bcp adds the prefix length.

    重要

    使用本机模式时,默认情况下, bcp 实用工具 先将 SQL ServerSQL Server 中的字符转换为 OEM 字符,然后将这些字符复制到数据文件中。When native mode is used, by default, the bcp utility converts characters from SQL ServerSQL Server to OEM characters before it copies them to a data file. bcp 实用工具 先将数据文件中的字符转换为 ANSI 字符,然后将这些字符批量导入到 SQL ServerSQL Server 表中。The bcp utility converts characters from a data file to ANSI characters before it bulk imports them into a SQL ServerSQL Server table. 在执行这些转换过程中,可能丢失扩展字符数据。During these conversions, extended character data can be lost. 对于扩展字符,请使用 Unicode 本机格式或指定代码页。For extended characters, either use Unicode native format or specify a code page.

  • sql_variant 数据sql_variant data

    如果 sql_variant 数据以 SQLVARIANT 存储在本机格式数据文件中,则数据会保留其所有特征。If sql_variant data is stored as a SQLVARIANT in a native-format data file, the data maintains all of its characteristics. 记录每个数据值的数据类型的元数据与数据值一起存储。The metadata that records the data type of each data value is stored along with the data value. 此元数据用于在目标 sql_variant 列中重新创建具有相同数据类型的数据值。This metadata is used to re-create the data value with the same data type in a destination sql_variant column.

    如果目标列的数据类型不是 sql_variant,则每个数据值将按照隐式数据转换的一般规则转换为目标列的数据类型。If the data type of the destination column is not sql_variant, each data value is converted to the data type of the destination column, following the normal rules of implicit data conversion. 如果在数据转换过程中出现错误,则回滚当前批。If an error occurs during data conversion, the current batch is rolled back. sql_variant 列之间传输的任何 charvarchar 值都可能存在代码页转换问题。Any char and varchar values that are transferred between sql_variant columns may have code page conversion issues.

    有关数据转换的详细信息,请参阅数据类型转换(数据库引擎)For more information about data conversion, see Data Type Conversion (Database Engine).

本机格式的命令选项Command Options for Native Format

可以使用 bcpBULK INSERTINSERT ... 将本机格式数据导入表中SELECT * FROM OPENROWSET(BULK...)。对于 bcp 命令或 BULK INSERT 语句,你可以在语句中指定数据格式。You can import native 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. 对于 INSERT ...SELECT * FROM OPENROWSET(BULK...) 语句,必须在格式化文件中指定数据格式。For an INSERT ... SELECT * FROM OPENROWSET(BULK...) statement, you must specify the data format in a format file.

下列命令选项支持本机格式:Native format is supported by the following command options:

CommandCommand 选项Option 描述Description
bcpbcp -n-n 使 bcp 实用工具使用本机数据类型的数据。*Causes the bcp utility to use the native data types of the data.*
BULK INSERTBULK INSERT DATAFILETYPE ='native'DATAFILETYPE ='native' 使用本机数据类型或宽本机数据类型的数据。Uses the native or wide native data types of the data. 注意,如果格式化文件指定了数据类型,则不需要 DATAFILETYPE。Note that DATAFILETYPE is not needed if a format file specifies the data types.
OPENROWSETOPENROWSET 空值N/A 必须使用格式化文件Must use a format file

*若要将本机 ( -n) 数据加载到与早期版本的 SQL ServerSQL Server 客户端兼容的格式,请使用 -V 开关。*To load native (-n) data to a format compatible with earlier versions of SQL ServerSQL Server clients, use the -V switch. 有关详细信息,请参阅 导入来自早期版本的 SQL Server 的本机格式数据和字符格式数据For more information, see Import Native and Character Format Data from Earlier Versions of SQL Server.

备注

或者,您可以在格式化文件中为每个字段指定格式设置。Alternatively, you can specify formatting on a per-field basis in a format file. 有关详细信息,请参阅 用来导入或导出数据的格式化文件 (SQL Server)For more information, see Format Files for Importing or Exporting Data (SQL Server).

示例测试条件Example Test Conditions

本主题中的示例基于下面定义的表和格式化文件。The examples in this topic are based on the table, and format file defined below.

示例表Sample Table

以下脚本将创建测试数据库、名为 myNative 的表并用一些初始值填充表。The script below creates a test database, a table named myNative 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.myNative ( 
   PersonID smallint NOT NULL,
   FirstName varchar(25) NOT NULL,
   LastName varchar(30) NOT NULL,
   BirthDate date,
   AnnualSalary money
   );

-- Populate table
INSERT TestDatabase.dbo.myNative
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.myNative;

示例非 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. 有关详细信息,请查看 非 XML 格式化文件 (SQL Server)Please review Non-XML Format Files (SQL Server) for detailed information. 下面的命令基于 的架构使用 bcp 实用工具 myNative.fmt生成非 XML 格式化文件 myNativeThe following command will use the bcp utility to generate a non-xml format file, myNative.fmt, based on the schema of myNative. 若要使用 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 commands:

bcp TestDatabase.dbo.myNative format nul -f D:\BCP\myNative.fmt -T -n 

REM Review file
Notepad D:\BCP\myNative.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 Native Format to Export Data

-n 切换和 OUT 命令。-n 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 commands:

bcp TestDatabase.dbo.myNative OUT D:\BCP\myNative.bcp -T -n

REM Review results
NOTEPAD D:\BCP\myNative.bcp

在不使用格式化文件的情况下使用 bcp 和本机格式导入数据Using bcp and Native Format to Import Data without a Format File

-n 切换和 IN 命令。-n switch and IN command. 在命令提示符处输入以下命令:At a command prompt, enter the following commands:

REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.myNative;"

REM Import data
bcp TestDatabase.dbo.myNative IN D:\BCP\myNative.bcp -T -n

REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.myNative;"

在使用非 XML 格式化文件的情况下使用 bcp 和本机格式导入数据Using bcp and Native Format to Import Data with a Non-XML Format File

-n-f 切换以及 IN 命令。-n and -f switches and IN command. 在命令提示符处输入以下命令:At a command prompt, enter the following commands:

REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.myNative;"

REM Import data
bcp TestDatabase.dbo.myNative IN D:\BCP\myNative.bcp -f D:\BCP\myNative.fmt -T

REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.myNative;"

在不使用格式化文件的情况下使用 BULK INSERT 和本机格式Using BULK INSERT and Native 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.myNative; -- for testing
BULK INSERT TestDatabase.dbo.myNative
    FROM 'D:\BCP\myNative.bcp'
    WITH (
        DATAFILETYPE = 'native'
        );

-- review results
SELECT * FROM TestDatabase.dbo.myNative;

在使用非 XML 格式化文件的情况下使用 BULK INSERT 和本机格式Using BULK INSERT and Native 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.myNative; -- for testing
BULK INSERT TestDatabase.dbo.myNative
   FROM 'D:\BCP\myNative.bcp'
   WITH (
        FORMATFILE = 'D:\BCP\myNative.fmt'
        );

-- review results
SELECT * FROM TestDatabase.dbo.myNative;

在使用非 XML 格式化文件的情况下使用 OPENROWSET 和本机格式Using OPENROWSET and Native 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.myNative;  -- for testing
INSERT INTO TestDatabase.dbo.myNative
    SELECT *
    FROM OPENROWSET (
        BULK 'D:\BCP\myNative.bcp', 
        FORMATFILE = 'D:\BCP\myNative.fmt'  
        ) AS t1;

-- review results
SELECT * FROM TestDatabase.dbo.myNative;

相关任务Related Tasks

使用数据格式进行批量导入或批量导出To use data formats for bulk import or bulk export

另请参阅See Also

bcp Utility bcp Utility
BULK INSERT (Transact-SQL) BULK INSERT (Transact-SQL)
数据类型 (Transact-SQL) Data Types (Transact-SQL)
sql_variant (Transact-SQL) sql_variant (Transact-SQL)
导入来自早期版本的 SQL Server 的本机格式数据和字符格式数据 Import Native and Character Format Data from Earlier Versions of SQL Server
OPENROWSET (Transact-SQL) OPENROWSET (Transact-SQL)
使用 Unicode 本机格式导入或导出数据 (SQL Server)Use Unicode Native Format to Import or Export Data (SQL Server)