Specifying File Storage Type by Using bcp
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.
Note
For information about data conversion, see Data Type Conversion (Database Engine).
The bcp Prompt for File Storage Type
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 type Enter at command prompt char1
c[har]
varchar
c[har]
nchar
w
nvarchar
w
text2
T[ext]
ntext2
W
binary
x
varbinary
x
image2
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
UDT (a user-defined data type)
U
XML
X
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. For more information, see Storage of Data in Character Format.
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. For more information, see Using Large-Value Data Types.
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 |
|---|---|
char1 |
SQLCHAR |
varchar |
SQLCHAR |
nchar |
SQLNCHAR |
nvarchar |
SQLNCHAR |
text2 |
SQLCHAR |
ntext2 |
SQLNCHAR |
binary |
SQLBINARY |
varbinary |
SQLBINARY |
image2 |
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 |
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.
Additional Considerations for File Storage Types
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.
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 Understanding Non-XML Format Files.
See Also
Concepts
Specifying Field Length by Using bcp
Specifying Field and Row Terminators
Specifying Prefix Length in Data Files
Storage of Data in Character Format
Other Resources
bcp Utility
Data Types (Transact-SQL)