306 Part I EXAM PREPARATION BCP Speed and
306 Part I EXAM PREPARATION BCP Speed and File Format What makes one method of BCP faster than another? Native format files are the smallest; character-delimited files are the next smallest; and column- delimited files are the largest. BCP is so fast and well optimized that it is bound by how fast it can read or write data to or from the file. NOTE also the fastest format, so use it whenever you can. The second fastest format is character-delimited. Character-delimited formats use some character, typically a comma, space, or a vertical bar, to separate the data columns. Finally, there is column-delimited data, which means the columns within the data file start and end at specific positions in the file. This is also called fixed column width or just fixed column data format. This tends to be the slowest way to BCP data around. BCP does not create tables. You have to have a table set up and waiting for BCP before you run BCP. So, how do you run BCP? Here s an example of reading data from a comma-delimited text file into a database table: bcp chapter5.dbo.sales in sales.csv -T -c -rn -t, The -T tells BCP to use a trusted connection. There is no server specified; it would be specified with the -S option, so the data goes to the local server. The -c tells BCP that it s supposed to use a character-delimited copy; the -r says that each row will be delimited with a newline character; and the -t says that each column will be delimited with a comma. BCP has a bunch of command-line parameters. Table 5.3 lists the ones that are used for determining the file format. TABLE 5.3 BCP COMMAND-LINE PARAMETERS FORMAT PARAMETERS Parameter Function -c Specifies a character-delimited file is to be used. -r Specifies the end-of-line character. Usually this is specified as r n, which specifies that there is a new line at the end of each line. -t Specifies the end-of-field character, typically a comma, vertical bar, or sometimes a space. Commas can be specified as -t, but any delimiter can be specified in double-quotes, such as -t | -f Specifies a format file to use. This is typically used to handle delimited data. -n Specifies that BCP should use native mode for copying. This parameter will copy all normal character data and non-character data okay, but will destroy any Unicode values.
For high quality website hosting services please check tomcat web hosting website.