Using a Format File to Map Fields to Columns During Bulk Import

A format file lets you control how you bulk import data from a data file to a Microsoft SQL Server table. Format files are particularly useful when a mismatch exists between fields in the data file and columns in the table. By using a format file, you can bulk import data without having to add or delete unnecessary data or to reorder existing data in the data file.

Bulk Import Scenarios with Format Files

The topics in this section illustrate format files for several typical bulk-import scenarios in which the fields of the data file differ in number or order from the columns of the table. For each scenario, samples are provided of a non-XML format file and of an XML format file.

The scenarios are as follows:

Scenario	Topic
There are fewer fields in the data file than columns in the table.	Using a Format File to Skip a Table Column
There are more fields in the data file than columns in the table.	Using a Format File to Skip a Data Field
The fields in the data file are in a different order from the columns in the table.	Using a Format File to Map Table Columns to Data-File Fields

See Also

Reference

bcp Utility

BULK INSERT (Transact-SQL)

OPENROWSET (Transact-SQL)

Concepts

Creating a Format File