Specify File Storage Type by Using bcp (SQL Server)

 

The file storage type describes how data is stored in the data file. 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.

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:

  • To bulk export data from an instance of Microsoft SQL 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.

  • To bulk export data from an instance of SQL Server to a data file in character format, specify char as the file storage type for all columns in the table.

  • To bulk import data to an instance of SQL 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 typeEnter at command prompt
    char 1c[har]
    varcharc[har]
    ncharw
    nvarcharw
    text 2T[ext]
    ntext2W
    binaryx
    varbinaryx
    image 2I[mage]
    datetimed[ate]
    smalldatetimeD
    timete
    datede
    datetime2d2
    datetimeoffsetdo
    decimaln
    numericn
    floatf[loat]
    realr
    Inti[nt]
    bigintB[igint]
    smallints[mallint]
    tinyintt[inyint]
    moneym[oney]
    smallmoneyM
    bitb[it]
    uniqueidentifieru
    sql_variantV[ariant]
    timestampx
    UDT (a user-defined data type)U
    XMLX

    1 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.

    2 The ntext, text, and image data types will be removed in a future version of SQL Server. In new development work, avoid using these data types, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.

Each native file storage type is recorded in the format file as a corresponding host file data type.

File storage typeHost file data type
char 1SQLCHAR
varcharSQLCHAR
ncharSQLNCHAR
nvarcharSQLNCHAR
text 2SQLCHAR
ntext 2SQLNCHAR
binarySQLBINARY
varbinarySQLBINARY
image 2SQLBINARY
datetimeSQLDATETIME
smalldatetimeSQLDATETIM4
decimalSQLDECIMAL
numericSQLNUMERIC
floatSQLFLT8
realSQLFLT4
intSQLINT
bigintSQLBIGINT
smallintSQLSMALLINT
tinyintSQLTINYINT
moneySQLMONEY
smallmoneySQLMONEY4
bitSQLBIT
uniqueidentifierSQLUNIQUEID
sql_variantSQLVARIANT
timestampSQLBINARY
UDT (a user-defined data type)SQLUDT

1 Data files that are stored in character format use char as the file storage type. Therefore, for character data files, SQLCHAR is the only data type that appears in a format file.

2 You cannot bulk import data into text, ntext, and image columns that have DEFAULT values.

When you bulk export data from an instance of SQL Server to a data file:

  • You can always specify char as the file storage type.

  • 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.

  • 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 Server native format.

    System_CAPS_ICON_note.jpg Note


    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. For more information on non-XML format files, see Non-XML Format Files (SQL Server).

bcp Utility
Data Types (Transact-SQL)
Specify Field Length by Using bcp (SQL Server)
Specify Field and Row Terminators (SQL Server)
Specify Prefix Length in Data Files by Using bcp (SQL Server)

Show: