Importing/Exporting Data  «Prev 

(BCP) Bulk Copy Program in SQL Server

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.