Chapter 20. Importing and Exporting SQL Server Data Using BCP and DTS

by Paul Bertucci

IN THIS CHAPTER

• Bulk-Copy Program (BCP)

• The Bulk-Copy Program

• Logged and Non-Logged Operations

• The BULK INSERT Statement (Transact-SQL)

• Improving Load Performance

• BCP Extras

• Data Transformation Services (DTS)

• DTS Architecture and Concepts

• Package Execution Utilities

• Running the DTS Wizard

• DTS Designer

• A Bit More on Metadata

The Bulk-Copy Program (BCP) is like an old friend to anyone who needs to get data in to (import) SQL Server tables from flat files or get data out of (export) SQL Server tables in to flat files. In fact, it is more like a warm security blanket because of the ease with which large amounts of data can be loaded or unloaded and the extreme reliability of that data movement. The good news with the SQL Server 2000 version of BCP is that it can handle most data types (including Image and Identity data types). In older versions of SQL Server, BCP was much more limited. And BCP can be extremely fast in its execution. This fact alone probably accounts for its continued presence in the MS SQL Server 2000 product.

Taking over the reigns for BCP and extending it into complex data transformations is Microsoft's Data Transformation Services (DTS). This chapter will describe the DTS environment and how DTS is addressing more complex data needs. The focus will be on importing, exporting, and transforming data from one or more datasources to one or more data targets. Other Microsoft solutions exist for importing and exporting data, but DTS can be used for a larger variety of data transformation purposes, and its strength is in direct data access and complex data transformation.

The alternatives to DTS and BCP in the Microsoft SQL Server 2000 environment include Replication, Distributed Queries, BULK INSERT , and SELECT INTO / INSERT . This chapter will help you determine how and when to use both BCP and DTS. And, more importantly, for what requirements.