使用 bcp 指定文件存储类型 (SQL Server)Specify File Storage Type by Using bcp (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

“文件存储类型” 说明数据在数据文件中的存储方式。The file storage type describes how data is stored in the data file. 数据可以按其数据库表类型(本机格式)、字符表示形式(字符格式)或支持隐式转换的任何数据类型导出到数据文件中;例如,以 int 形式复制 smallint。用户定义的数据类型将按其基类型导出。Data can be exported to a data file as its database table type (native format), in its character representation (character format), or as any data type where implicit conversion is supported; for example, copying a smallint as an int. User-defined data types are exported as their base types.

用于文件存储类型的 bcp 提示符The bcp Prompt for File Storage Type

如果某个交互式 bcp 命令包含不带格式化文件开关 ( -f ) 或数据格式开关( -n-c-w-N)的 inout选项,则该命令会提示输入每个数据字段的文件存储类型,如下所示:If an interactive bcp command contains the in or out option without either the format file switch (-f) or a data-format switch (-n, -c, -w, or -N), the command prompts for the file storage type of each data field, as follows:

Enter the file storage type of field <field_name> [<default>]:

您对此提示符的响应取决于要执行的任务,如下所示:Your response to this prompt depends on the task you perform, as follows:

  • 若要以尽可能大的压缩存储的格式(本机数据格式)将数据从 MicrosoftMicrosoft SQL ServerSQL Server 实例批量导出到数据文件中,请接受 bcp提供的默认文件存储类型。To bulk export data from an instance of MicrosoftMicrosoft SQL ServerSQL Server to a data file in the most compact storage possible (native data format), accept the default file storage types that are provided by bcp. 有关本机文件存储类型的列表,请参阅本主题后面所述的“本机文件存储类型”。For a list of the native file storage types, see "Native File Storage Types," later in this topic.

  • 若要以字符格式将数据从 SQL ServerSQL Server 实例大容量导出到数据文件中,请指定 char 作为表中所有列的文件存储类型。To bulk export data from an instance of SQL ServerSQL Server to a data file in character format, specify char as the file storage type for all columns in the table.

  • 若要将数据从数据文件大容量导入到 SQL ServerSQL Server 实例,对于以字符格式存储的类型,请将文件存储类型指定为 char ,而对于以本机数据类型格式存储的数据,请按需指定以下文件存储类型之一:To bulk import data to an instance of SQL ServerSQL Server from a data file, specify the file storage type as char for types stored in character format and, for data stored in native data type format, specify one of the file storage types, as appropriate:

    文件存储类型File storage type 在命令提示符下输入Enter at command prompt
    charchar c[har]c[har]
    varcharvarchar c[har]c[har]
    ncharnchar ww
    nvarcharnvarchar ww
    texttext T[ext]T[ext]
    ntext2ntext2 WW
    binarybinary xx
    varbinaryvarbinary xx
    图像image I[mage]I[mage]
    datetimedatetime d[ate]d[ate]
    smalldatetimesmalldatetime DD
    timetime tete
    datedate dede
    datetime2datetime2 d2d2
    datetimeoffsetdatetimeoffset dodo
    decimaldecimal nn
    numericnumeric nn
    floatfloat f[loat]f[loat]
    realreal rr
    IntInt i[nt]i[nt]
    bigintbigint B[igint]B[igint]
    intsmallint s[mallint]s[mallint]
    tinyinttinyint t[inyint]t[inyint]
    moneymoney m[oney]m[oney]
    smallmoneysmallmoney MM
    bitbit b[it]b[it]
    uniqueidentifieruniqueidentifier uu
    sql_variantsql_variant V[ariant]V[ariant]
    timestamptimestamp xx
    UDT (用户定义的数据类型)UDT (a user-defined data type) UU
    XMLXML XX

    *字段长度、前缀长度和终止符一起决定了在以 char 文件存储类型导出的非字符数据的数据文件中分配的存储空间量。*The interaction of field length, prefix length, and terminators determines the amount of storage space that is allocated in a data file for noncharacter data that is exported as the char file storage type.

    **的未来版本中将删除ntext text image SQL ServerSQL Server数据类型。** The ntext, text, and image data types will be removed in a future version of SQL ServerSQL Server. 在新的开发工作中,请避免使用这些数据类型,并修改当前使用它们的应用程序。In new development work, avoid using these data types, and plan to modify applications that currently use them. 请改用 nvarchar(max)varchar(max)varbinary(max)Use nvarchar(max), varchar(max), and varbinary(max) instead.

本机文件存储类型Native File Storage Types

在格式化文件中,每种本机文件存储类型都记录为相应的宿主文件数据类型。Each native file storage type is recorded in the format file as a corresponding host file data type.

文件存储类型File storage type 宿主文件数据类型Host file data type
charchar SQLCHARSQLCHAR
varcharvarchar SQLCHARSQLCHAR
ncharnchar SQLNCHARSQLNCHAR
nvarcharnvarchar SQLNCHARSQLNCHAR
texttext SQLCHARSQLCHAR
ntextntext SQLNCHARSQLNCHAR
binarybinary SQLBINARYSQLBINARY
varbinaryvarbinary SQLBINARYSQLBINARY
图像image SQLBINARYSQLBINARY
datetimedatetime SQLDATETIMESQLDATETIME
smalldatetimesmalldatetime SQLDATETIM4SQLDATETIM4
decimaldecimal SQLDECIMALSQLDECIMAL
numericnumeric SQLNUMERICSQLNUMERIC
floatfloat SQLFLT8SQLFLT8
realreal SQLFLT4SQLFLT4
smallintint SQLINTSQLINT
bigintbigint SQLBIGINTSQLBIGINT
smallintsmallint SQLSMALLINTSQLSMALLINT
tinyinttinyint SQLTINYINTSQLTINYINT
moneymoney SQLMONEYSQLMONEY
smallmoneysmallmoney SQLMONEY4SQLMONEY4
bitbit SQLBITSQLBIT
uniqueidentifieruniqueidentifier SQLUNIQUEIDSQLUNIQUEID
sql_variantsql_variant SQLVARIANTSQLVARIANT
timestamptimestamp SQLBINARYSQLBINARY
UDT(用户定义的数据类型)UDT (a user-defined data type) SQLUDTSQLUDT

*以字符格式存储的数据文件使用 char 作为文件存储类型。*Data files that are stored in character format use char as the file storage type. 因此,对于字符数据文件,SQLCHAR 是唯一出现在格式化文件中的数据类型。Therefore, for character data files, SQLCHAR is the only data type that appears in a format file.

**你无法将数据批量导入到具有 DEFAULT 值的 textntextimage 列。**You cannot bulk import data into text, ntext, and image columns that have DEFAULT values.

文件存储类型的其他注意事项Additional Considerations for File Storage Types

当您将数据从 SQL ServerSQL Server 实例大容量导出到数据文件时:When you bulk export data from an instance of SQL ServerSQL Server to a data file:

  • 您始终可以指定 char 作为文件存储类型。You can always specify char as the file storage type.

  • 如果您输入的文件存储类型表示无效的隐式转换, bcp 将失败;例如,尽管您可以为 int 数据指定 smallint 类型,但是如果您为 smallint 数据指定 int 类型,将导致溢出错误。If you enter a file storage type that represents an invalid implicit conversion, bcp fails; for example, though you can specify int for smallint data, if you specify smallint for int data, overflow errors result.

  • 当非字符数据类型(如 floatmoneydatetimeint )存储为其数据库类型时,数据将写入 SQL ServerSQL Server 本机格式的数据文件。When noncharacter data types such as float, money, datetime, or int are stored as their database types, the data is written to the data file in the SQL ServerSQL Server native format.

    备注

    在你以交互方式指定 bcp 命令中的所有字段后,该命令会提示你将自己对每个字段的响应保存到一个非 XML 格式化文件中。After you interactively specify all of the fields in a bcp command, the command prompts you save your responses for each field in a non-XML format file. 有关非 XML 格式文件的详细信息,请参阅 非 XML 格式化文件 (SQL Server)For more information on non-XML format files, see Non-XML Format Files (SQL Server).

另请参阅See Also

bcp 实用工具 bcp Utility
数据类型 (Transact-SQL) Data Types (Transact-SQL)
使用 bcp 指定字段长度 (SQL Server) Specify Field Length by Using bcp (SQL Server)
指定字段终止符和行终止符 (SQL Server) Specify Field and Row Terminators (SQL Server)
使用 bcp 指定数据文件中的前缀长度 (SQL Server)Specify Prefix Length in Data Files by Using bcp (SQL Server)