In a previous post, I discussed using BCP (Bulk Copy) from the command line in order to quickly export tables to flat files for backups or just transporting the data.

When you are doing bulk copy from the command prompt, you are asked a series of questions about the data types of each field in the table you are copying. At the very end you are asked the name of the format file to store the descriptive information (the default filename for this file is bcp.fmt). Well, where does this .fmt file come into the picture? What is it used for?

It is used when you have to now reload that table using the “bulk insert” command in Query Analyzer. It has the descriptors for the target table and will know how to load it based on the information in it.

In my example of using BCP to export the Customer table, we created a file named customers.txt and a .fmt file name bcp.fmt. To load this table now into a like-formatted target table called CustomerCopy, we invoke the following command in Query Analyzer:

bulk insert CustomerCopy FROM 'C:\test\customers.txt' WITH (FORMATFILE = 'C:\test\bcp.fmt')