Importing/Exporting Data  «Prev  Next»

Lesson 5The bulk copy program (bcp)
Objective Use the bulk copy program.

SQL Bulk Copy Program in SQL - Server

Like the bulk insert statement, the bulk copy program (bcp) can be used to load data from a file into a table. However, unlike the bulk insert statement, bcp can also be used to extract data from a table. The parameters for the bcp program are similar to the bulk insert statement options. The following MouseOver illustrates the syntax of the bulk copy program.


(BCP) Bulk Copy Program in SQL Server

bulk-copy consisting of dbtable, query, in | out | queryout | format
bulk-copy consisting of dbtable, query, in | out | queryout | format

  1. dbtable – The permanent table, global temporary table, or view to access.
  2. query – bcp can extract data from a query, the query must be enclosed in double quotes(“query”).
  3. in | out | queryout | format – Specifies what the bcp is being used for.
  4. Datafile – The file to export/import; you must specify the datafile.
  5. -m maxerrors – The maximum number of errors before the bcp is aborted; this parameter defaults to 10.
  6. -f formatfile – Specifies the file that contains information; defaults to no format file.
  7. -e errfile – A file that all records that generate errors will be copied to. If you do not specify a error file, SQL Server will print error information to the screen, but will not tell you what row caused the problem; defaults to no errorfiles.
  8. -F firstrow – Specifies the first row from the file to load, or the first row from the table to export. Defaults to the first row.
  9. -L lastrow – Specifies the last row to load, or the last row to export; defaults to all the rows.
  10. -b batchsize – Specifies how many rows to import as one transaction; defaults to all the file
  11. -n – Data is stored as SQL Server’s native type; defaults to not being set.
  12. -c – Data is stored as character; defaults to not being set.
  13. -w – Data is unicode character; defaults to not being set.
  14. -N – Non-text data is native format; defaults to not being set.
  15. -6 – The data is 6x file format; defaults to not being set.
  16. -q – Quoted identifier specifies that the dbtable string will either contain quotes or brackets to delimit object names; defaults tonot being set.
  17. -C code page – Specifies the code page that is used to store character data; defaults to SQL Servers standard code page
  18. -t field terminator – Specifies the field terminator; default is a tab (\t)
  19. -r row terminator – Specifies the row terminator; default is a newline (\n)
  20. -i inputfile – Allows you to specify a file that contains the answers to questions describing the format of the bcp; defaults to noinput file.
  21. -o outfile – Allows you to specify a file in which bcp will store its output; defaults to no output file
  22. -a packetsize – Allows you to specify how many bytes will be sent between SQL Server and bcp in a newtwork packet; defaults to SQL Server’s configured value.
  23. -S server name – The SQL Server to connect with; defaults to the local server.
  24. -U username – The SQL Server standard security account to use when connecting to SQL Server; defaults to your Windows Login name.
  25. -P password – The password to use when connecting to SQL Server. If a password is not provided, bcp will prompt you for one; there is no default.
  26. -T – bcp will connect using a trusted connection, using integrated security; defaults to not being set.
  27. -v – bcp will display its version; defaults to not being set.
  28. -R – The client settings for date, time, and currency formats will be used; defaults to not being set.
  29. -k – SQL will not apply default values for columns that are not loaded as part of the bcp operation; defaults to not being set.
  30. -E – SQL will not generate new identity values for tables containing identity types; defaults to not being set.
  31. -h – "load hints" – Allows you to control the behavior of bcp by specifying one of the following – CHECK_CONSTRAINTS, KILLOBYTES_PER_BATCH, ORDER, ROWS_PER_BATCH, or TABLOCK. These all behave the same as the BULK INSERT statements options.

When performing bulk copies, you should first know the following information:
  1. When using a query, you can specify any valid SQL statement.
  2. If you are using a query and it produces multiple result sets (either from calling a stored procedure, or having a compute by[1] clause), the bcp will only extract the first result set.
  3. If a data file is missing, the first parameter after the direction ( IN, OUT, FORMAT, QUERYOUT) is treated as the data file name.
  4. The –E parameter has no effect if the file does not contain data for the identity column.
  5. If –n, -c, -w, -6 , -N or –f are not specified, bcp will prompt you for format information about each column.
    If one of –n, -c, -w, -6 , -N or –f is specified, a \r will be used as the row terminator and a \t will be used as a field terminator.
  6. In = Import data; out = export data; queryout = you are using a query to export data; format = generate a format file without extracting data--this must be used with the –f option.

Example

bcp Sales.employee in empl.data 
-S WesternRegion –T -c

  1. The example above loads data from the file empl.data into the Sales database.
  2. The employee table is on the WesternRegion database server and uses integrated security.
  3. This file has data stored in character format, with a tab between fields and a carriage return at the end of each record.
While bcp allows you to transfer data between SQL Server and files, often times you will want to move data between other data sources. The next lesson will introduce you to the basics of the data transformation services.
[1] Compute by: A TSQL extension that allows you to have summary rows intermixed with detail rows.