BCP


BCP (bulk copy program) is a tool that is used to address the bulk movement of data. The utility is bidirectional, allowing for the movement of data in and out of a SQL Server database.

The SQL Server 2000 version of the Bulk copy program (BCP) utilizes the ODBC bulk copy API instead of the DB-LIB API that was used in earlier versions of SQL Server. The ODBC bulk copy API was used to support new data types that the DB-Library API does not support. Backward compatibility options are provided with the SQL Server 2000 BCP utility to allow bulk copy of data types supported in earlier versions.

BCP uses the following syntax:

 BCP {[[  database_name  .][  owner  ].]{  table_name   view_name  }  "  query  "}{      in  out  queryout  format}  data_file  [-F first_row] [-L last_row] [-t] [-E] 

BCP also works in conjunction with the query optimizer so that importing and exporting data is more efficient than in earlier versions. For a more detailed look at BCP, see Chapter 20, "Importing and Exporting SQL Server Data Using BCP and DTS."

Some of the commonly used options ”other than the ones used to specify the database, such as user ID, password, and so on ”are the -F and -L options. These options allow you to specify the first and last row of data, which is especially helpful in large batches. The -t option allows you to specify the field terminator that separates data elements in an ASCII file. The -E option allows you to import data into SQL Server fields that are defined with identity properties.

TIP

The BULK INSERT Transact-SQL statement and Data Transformation Services (DTS) are good alternatives to BCP. The BULK INSERT statement is limited to loading data into SQL Server, but has been deemed the fastest bulk copy method by Microsoft. DTS has a sophisticated GUI that allows for both data import and data export.

The BCP.EXE file is located, by default, with the SQL Server tools in Microsoft SQL Server\80\Tools\Binn\ .



Microsoft SQL Server 2000 Unleashed
Microsoft SQL Server 2000 Unleashed (2nd Edition)
ISBN: 0672324679
EAN: 2147483647
Year: 2002
Pages: 503

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net