Using Format Files
When bulk copying data using interactive mode, the bcp utility prompts you to store information regarding the storage type, prefix length, field length, and field and row terminators. The file used to store the format information for each field in the data file is called the format file:
Do you want to save this format information in a file? [Y/n] y
Host filename: [bcp.fmt]
Although the default name for the format file is Bcp.fmt, a different file name can be specified.
This format file provides the default information used either to bulk copy the data in the data file back into an instance of Microsoft® SQL Server™ or to bulk copy data out from the table another time, without needing to respecify the format. When bulk copying data into or out of an instance of SQL Server with an existing format file, bcp does not prompt for the file storage type, prefix length, field length, or field terminator because it uses the values already recorded.
To use a previously created format file when importing data into an instance of SQL Server, use the -f switch with the bcp utility or the FORMATFILE clause with the BULK INSERT statement. For example, the command to bulk copy the contents of New_auth.dat data file into the authors2 table in the pubs database using the previously created format file (Authors.fmt) is:
bcp pubs..authors2 in c:\new_auth.dat -fc:\authors.fmt -Sservername -Usa -Ppassword
The BULK INSERT statement can use format files saved by the bcp utility. For example:
BULK INSERT pubs..authors2 FROM 'c:\new_auth.dat'
WITH (FORMATFILE = 'c:\authors.fmt')
The format file is a tab-delimited text file with a specific structure.
The following table describes the file format structures.
| Field | Description |
|---|---|
| Version | Version number of bcp. |
| Number of fields | Number of fields in the data file. This must be the same for all rows. |
| Host file field order | Position of each field within the data file. The first field in the row is 1, and so on. |
| Host file data type | Data type stored in the particular field of the data file. With ASCII data files, use SQLCHAR; for native format data files, use default data types. For more information, see File Storage Type. |
| Prefix length | Number of length prefix characters for the field. Legal prefix lengths are 0, 1, 2, and 4. To avoid specifying the length prefix, set this to 0. A length prefix must be specified if the field contains null data values. For more information, see Prefix Length. |
| Host file data length | Maximum length, in bytes, of the data type stored in the particular field of the data file. For more information, see Field Length. |
| Terminator | Delimiter to separate the fields in a data file. Common terminators are comma (,), tab (\t), and end of line (\r\n). For more information, see Field Terminator. |
| Server column order | Order that columns appear in the SQL Server table. For example, if the fourth field in the data file maps to the sixth column in a SQL Server table, then for the fourth field the server column order is 6. To omit a column in the table from receiving any data in the data file, set the server column order value to 0. |
| Server column name | Name of the column taken from the SQL Server table. It is not necessary to use the actual name of the field. The only condition is that the field in the format file not be blank. |
| Collation | The collation used to store character and Unicode data in the bulk copy data file. |
Note It is possible to skip importing a table column if the field does not exist in the data file by specifying 0 prefix length, 0 length, 0 server column order, and no terminator. This effectively states that the data field does not exist in the data file, and that the server column should not have data loaded into it.
Selectively Copying Data
A format file provides a way to bulk copy data selectively from a data file to an instance of SQL Server. This allows the transfer of data to a table when there is a mismatch between fields in the data file and columns in the table. This approach can be used when the fields in the data file are:
- Fewer than the columns in the table.
- More than the columns in the table.
- In a different order from the columns in the table.
By using a format file, it is possible to bulk copy data into an instance of SQL Server without having to add or delete unnecessary data, or reorder existing data, in the data file.
The following three topics contain examples of selectively copying data. For the following examples, first make a copy of the authors table, named authors2, in the pubs database. To create a copy of the authors table, execute:
USE pubs
GO
SELECT * INTO authors2 FROM authors
GO