optional bcp arguments


Optional BCP Arguments
Option Description
-S instancename Specifies which instance of SQL Server BCP should connect to. If the –S option is not specified the local default instance is assumed.
-T Indicates that you wish BCP to connect using a trusted connection (aka Windows authentication).
-U loginname Used to specify a SQL authentication login name when not using –T.
-P password Used to specify the password associated with the –U login name.
-N Use a Unicode native data file type.
-n Use a native data file type (use only when you don't have Unicode data).
-c Use a character data file type.
-w Use a Unicode data file type.
-t fieldterminator Specifies the field terminator that should be used in delimited data files. If special characters are needed, or a space is part of the delimiter, surround the delimiter in double quotes ("). If no field terminator is specified, the tab character is used.
-rrowterminator Specifies the row terminator that should be used in delimited data files. As with the field terminator, if special characters are needed or a space is used in the delimiter, you can enclose the terminator in double quotes ("). If no row terminator is specified, a line feed (or new line) character is used ("\n").
-F firstrownum Specifies the row number of the first row of data. This can be used to skip over header rows. It can also be used with the –L argument to specify a range of rows to process if the same data file is being processed on multiple workstations.
  If you are using a source file that has the column names in the first row, you need to make sure to use the –F parameter to tell BCP to start processing thefile at the second row (-F 2). Otherwise, it will try to read the column names as field values.
-L lastrownum Specifies the row number of the last row of data to process. This can be used if the data file has a footer that you don't want to process. It can also be used with the –F argument to specify a range of rows to process if the same data file is being processed on multiple workstations.
-b batchsize Specifies the number of rows to include in a single batch. Each batch then ends up as a separate commit operation. The entire file is treated as a single batch by default. By using the –b option you can break the load up in multiple batches to help manage the load on the transaction log and possibly restart a load at a certain point if it fails partway through.
-f formatfile Specifies the path to the format file to use
-x Used to generate an XML-formatted format file. Use this in conjunction with the "format" operation and the –f option to specify the path of the file.
-h Allows you to specify a number of "hints" that mainly affect the performance of the BCP option. We will discuss some of them when we talk about performance.
Others Again, there are other options available. You should review the information on BCP in the SQL Server 2008 documentation.