Importing and Exporting Data with SQL Server

Type of Transferring Data
Transferring data, whether into or out of a database, can be a complex process. SQL Server provides several tools that help you in a variety of situations, including the Bulk Copy Program, the Select statement and other tools.

In this section we will see the two methods of transferring data. 1) Bulk Copy Program(BCP) and 2) Using Store Procedures.

Bulk Copy Program (BCP)
BCP is a command-line utility that can read and write files in both ASCII format and in native format. (Native format uses variable-length records with field type and length descriptors embedded in them.) BCP using native format is the fastest way to transfer data between databases in SQL Server or Sybase SQL Server. If you use BCP with ASCII files, the ASCII files can be generated as fixed-length records or delimited files, and you can choose the delimiters. We can use BCP when we want to transfer large amount of data from or Into an external file.

BCP Parameters
The command line for BCP is complicated. Improper use of the options can cause BCP to fail and create significant headaches. BCP runs in an interactive command-line mode, which lets you specify the format of the table to dump if a format file is not provided for export, but other options default unless they are specified on the command line. Knowing when an option can be left out is as important as knowing what the defaults are for other values. We describe the command line in detail here to try to prevent your frustration when you first attempt to use it. Be aware that the options are case-sensitive. Note also that the login information comes at the end, whereas in most other utilities it is the first thing you supply.
bcp [[database_name.]owner.]table_name {in out} datafile
[/m maxerrors] [/f formatfile] [/e errfile]
[/F firstrow] [/L lastrow] [/b batchsize]
[/n] [/c] [/E]
[/t field_term] [/r row_term]
[/i inputfile] [/o outputfile]
/U login_id [/P password] [/S servername] [/v] [/a packet_size]
[[database_name.]owner.]table_name — The table that data will be imported to or exported from. If the user’s ID does not have the desired database as its default, the database name must be specified. If the user ID supplied does not own the table, the owner must be specified and the user must have the appropriate permission. To import data into a table, the user must have Insert permission on the table. To export data from a table, the user must have Select permission on the table and on several of the system tables (sysobjects, sysindexes, and syscolumns). Usually the DBO or SA performs this task, in which case, permissions are not an issue.

{in out} — Specifies the direction of the data transfer, into the database table (in) or out of the database table (out).
datafile — The physical location of file to import or export to. This location must be in the path on the machine executing the BCP or you must specify the file with a complete path.
[/m maxerrors] — The number of errors per batch to ignore before stopping. The default value is 10. If you expect errors, you can raise the maximum number of errors to ignore to let the non-error data load completely.
[/f formatfile] — The physical location of a BCP format file to be used for export or import. You must use a format file if you are not using a native format or default character format (see the /n and /c parameters, below). This parameter must be supplied with a complete path. When you export data, the format file is used to create the required format. When you import a file, the format file specifies the fields in the data file and the columns that the data fields are to be imported into. If you do not specify the file when you’re exporting data, BCP will run interactively and create the format file you describe. If a format file is specified during import and the file does not exist, BCP will generate an error.
[/e errfile] — The physical location of a file to which BCP should write error messages and records with errors. You should specify the complete path. The default path is the directory from which the BCP executable is run.
[/F firstrow] — The row on which the import or export is to start. This parameter is particularly useful if you want to skip headers or break up large files into smaller batches.
[/L lastrow] — The row on which to stop the import or export. This parameter is useful for very large loads that need to be broken up.
[/b batchsize] — The number of records to load between committing transactions. The default value is all the records being inserted in one transaction. If this parameter is not set appropriately for the job, the load can cause transaction logs to become full (see the discussion about fast and slow BCP on page 266). It works with the number of errors to determine when it will abort.
[/n] [/c] — Native format or character format. These two options are mutually exclusive; you can specify a format with the /f parameter instead of using either. They are discussed in more detail on page 265.
[/E] — Turns off the identity insert, allowing files containing rows with identity column information to be inserted with their current identity value. Otherwise, the identity column is set according to the seed and increment defined at the time of creation. If you don’t want to preserve the identity column, it is best to use the format file to leave it out of the data export.
[/t field_term] — The field delimiter indicator; tab (\t) is the default. The most common delimiter is the double quote (“ ”) or the comma (,), but be aware that comma-delimited data will fail if the data records include commas. We recommend that you choose a character that you are sure is not in the data, such as the pipe () or tilde (~), if the source of the data supports it.
[/r row_term] — The row terminator, typically a carriage return/linefeed (\r \n).
[/i inputfile] — A file to replace standard input. You can use the file to provide the information BCP requests interactively.
[/o outputfile] — The physical path to the file to which standard output should be redirected. The file logs information such as the number of rows and other non-error output.
/U login_id — A valid login ID and user ID for the database(s) the tables are being imported to or exported from. The system administrator login and ID are used by default.
[/P password] — The correct password for the above user ID. The BCP utility will prompt for a password if none is provided.
[/S servername] — Server name to connect to. The default is the local server. BCP is faster if you use it locally instead of across the network.
[/v] — Gives the current version information for BCP. This information is important for the format files.
[/a packet_size] — Network packet size to be used in transferring data. Values can be between 512 and 36635. Larger packets can improve performance for larger BCP data transfers. Defaults for SQL on Windows NT are now 4096. This parameter can be set for SQL Server under the Configurations menu and overridden on a case-by-case basis through BCP. BCP reports information concerning the network packet size, number of rows processed, and the rate of processing. Microsoft recommends using a value of between 4096 and 8192 for SQL Server running on Windows NT.