Professional SQL Server™ 2005 Programming

18.2. Format Files

The use of format files was mentioned in the previous section. This is actually something of an exciting area, as a lot has happened in to our options for format files in SQL Server 2005.

Format files can be thought of as import templates and make it easier to support recurring import operations when:

• Source file and target table structures or collations do not match.

• You want to skip columns in the target table.

• Your file contains data that makes the default data typing and collation difficultor unworkable.

Beginning in this release, format files now come in two varieties: non-XML and XML. We will start off by looking at the "old" way of doing things (the non-XML version) and then take a look at the newer XML format files after we've checked how each non-XML version looks.

To get a better idea of how each type of format file works, let's look at some specific examples. First you'll see how the file is structured when the source and destination match. Next, youcan compare this to situations where the number of source file fields doesn't match the number of table columns or where source fields are ordered differently than the table columns.

You can create a default format file (which is non-XML for backward-compatibility reasons) to use as your source when you run BCP in interactive mode. After prompting for column value information, you're given the option of saving the file. The default filename is BCP.fmt, but you can give the format file any valid filename. ...