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

Concepts

Creating a Format File

Other Resources

bcp Utility
BULK INSERT (Transact-SQL)
OPENROWSET (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance