Specifying File Storage Type by Using bcp

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
  time	te
  date	de
  datetime2	d2
  datetimeoffset	do
  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.

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

Reference

Concepts