Problem One very common process that occurs in most SQL Server environments is the need to import and export data. With SQL Server 7.0 and 2000 we had DTS (Data Transformation Services) and now with SQL Server 2005 we have SSIS (SQL Server Integration Services). These are great GUI environments and give you a lot of flexibility to move data in and out of SQL Server. In addition to these GUI tools there is the tried and true BCP (Bulk Copy Program) that has been around for a lot longer then DTS or SSIS.
Even though this tool has been around quite some time, there are always enhancements and with SQL Server 2005 additional enhancements have been added as well. One of the features of BCP is to create a format file that defines the data that is coming into SQL Server and where it should go during the import process. With SQL Server 2005 an enhancement has been made so this format file can either be the standard format or in an XML format.
Solution Creating a format file for bcp is not new to SQL Server 2005 and these files can be created using older versions as well as with SQL Server 2005. This can be done by using a command similar to the following, which creates a format file based on the structure of the Categories table in the Northwind database.
bcp Northwind.dbo.Categories format nul -c -f categories.fmt -T -S servername
This creates a standard format file that can then be edited to handle differences in your source and destination formats. When you run this command, this is the output you get. For additional information take a look at books online.
Other then how to create and modify the format files, the rest of BCP still works the same. So you would still reference your format file just as before. The -x option is only used when creating the format file.