File Storage Type
The file storage type describes how data is stored in the data file. Data can be copied to a data file as its database table type (native format), as a character string in ASCII format (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 copied as their base types.
To bulk copy 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 provided by bcp.
To bulk copy data from an instance of SQL Server to a data file as ASCII text, specify char as the file storage type for all columns in the table.
To bulk copy data to an instance of SQL Server from a data file, specify the file storage type as char for ASCII-only files, and the following appropriate file storage type for data stored in native data type format.
| File storage type | Enter at command prompt |
|---|---|
| char | c[har] |
| varchar | c[har] |
| nchar | w |
| nvarchar | w |
| text | T[ext] |
| ntext | W |
| binary | x |
| varbinary | x |
| image | I[mage] |
| datetime | d[ate] |
| smalldatetime | D |
| decimal | n |
| numeric | n |
| float | f[loat] |
| real | r |
| Int | i[nt] |
| bigint | B[igint] |
| smallint | s[mallint] |
| tinyint | t[inyint] |
| money | m[oney] |
| smallmoney | M |
| Bit | b[it] |
| uniqueidentifier | u |
| sql_variant | V[ariant] |
| timestamp | x |
Entering a file storage type that represents an invalid implicit conversion causes bcp to fail. For example, specifying smallint for int data causes overflow errors, but specifying int for smallint data is valid. Specifying char as the file storage type when bulk copying any data type from an instance of SQL Server to a data file is always valid.
When noncharacter data types (for example, float, money, datetime, or int) are stored as their database types, the data is written to the data file in the SQL Server internal binary data format.
A format file can also be generated to save the responses of the file storage type for each field. This format file can be used to provide the default information used to bulk copy the data in the data file back into an instance of SQL Server, or to bulk copy data out from the table another time, without needing to respecify the format.
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 |
|---|---|
| char | SQLCHAR |
| varchar | SQLCHAR |
| nchar | SQLNCHAR |
| nvarchar | SQLNCHAR |
| text | SQLCHAR |
| ntext | SQLNCHAR |
| binary | SQLBINARY |
| varbinary | SQLBINARY |
| image | SQLBINARY |
| datetime | SQLDATETIME |
| smalldatetime | SQLDATETIM4 |
| decimal | SQLDECIMAL |
| numeric | SQLNUMERIC |
| float | SQLFLT8 |
| real | SQLFLT4 |
| int | SQLINT |
| bigint | SQLBIGINT |
| smallint | SQLSMALLINT |
| tinyint | SQLTINYINT |
| money | SQLMONEY |
| smallmoney | SQLMONEY4 |
| bit | SQLBIT |
| uniqueidentifier | SQLUNIQUEID |
| sql_variant | SQLVARIANT |
| timestamp | SQLBINARY |
Because data files stored as ASCII text use char as the file storage type, only SQLCHAR appears in the format file in those instances.