Search code examples
sql-serverbulkinsertbcp

How do you create an fmt file using the bcp command and only selecting a subset of columns from the target table?

I am creating .fmt files with the bcp command using the template below:

bcp schema.dbo.table format nul -S servername\sqlserver-engine -f filename.fmt -c -T

This command creates a tab separated file with all the columns from the target table along with appropriate data type/length specifications.

However, in my use case, only a subset of columns will have data, so in order to complete the bulk insert a manual process is needed to eliminate the columns that will be null and renumber the items left on the .fmt file. Is there a way to create the .fmt file with a subset of columns (only what is needed) in order to eliminate this manual process?

Solution

  • three options:

    1. create the format file yourself, programmatically. Do not use BCP with "format" option. Write a query against information_schema for the table you want. Put that query into a built bcp command and execute the command on the os. fyi, just need a single space between each "column" of format file. dont need to have it look exactly like what the BCP "format" command gives you.

    2. create a view on top of your table to be exported. eliminate unwanted columns there. bcp out that view.

    3. leave all as is for your export and just ignore the columns you dont like on the way into your destination. Cant set destination to "0" in the format file and it will be ignored.