Specifying Data Formats
If data is being copied between an instance of Microsoft® SQL Server™ and other programs, such as another database program, the default data type formats (native, character, or Unicode) may not be compatible with the data structures expected by the other programs. Therefore, the bcp utility allows more detailed information regarding the structure of the data file to be specified.
If the -n, -c, -w, or -N switches are not specified, the bcp utility prompts for further information interactively on each column of data being copied:
- File storage type
- Prefix length
- Field length
- Field terminator
Note Interactive mode is not available when using the BULK INSERT statement.
The bcp utility provides default values at each of these prompts based on the SQL Server data type of the source or destination column. Accepting the default values supplied by bcp at these prompts produces the same result as native format (-n), and provides a way to bulk copy data out of other programs for later reloading into SQL Server.
A format file can be created to store the responses of the prompts for each field in the data file, allowing the same responses to be reused without having to enter them again. The format file can be used to provide all the format information required to bulk copy data to and from an instance of SQL Server. A format file provides a flexible system for writing data files that requires little or no editing to conform to other data formats, or for reading data files from other software.
For example, the command to bulk copy the publishers table interactively to the Publ.txt file is:
bcp pubs..publishers out publ.txt -Sservername -Usa -Ppassword
A series of prompts appears for each column of the publishers table, with the bcp-supplied default displayed in brackets. This example is for the pub_id column in the publishers table only.
Enter the file storage type of field pub_id [char]:
Enter prefix length of field pub_id [0]:
Enter length of field pub_id [4]:
Enter field terminator [none]:
Pressing ENTER accepts the supplied default. To specify a value other than the default, enter the new value at the command prompt.