Chapter 24 -- Loading the Database | Microsoft SQL Server 2000 Administrators Companion

3 4

Chapter 24

After you create your database and database tables, you are ready to load your data. Several methods of loading data into a database are available; which method you choose depends on what the source of your data is, what sort of processing you want to perform on the data, and where you are loading the data. In this chapter, we'll look at the following methods of loading a database:

Using Bulk Copy Program (BCP) BCP is an external program provided with Microsoft SQL Server 2000 to facilitate the loading of data files into a database. BCP can also be used to copy data out of a SQL Server table into a data file.
Using the BULK INSERT command The BULK INSERT Transact-SQL (T-SQL) command lets you copy large quantities of data from a data file into a SQL Server table, from within SQL Server. Because this command is an SQL statement (run within ISQL, OSQL, or Query Analyzer), the process runs as a SQL Server thread. You cannot use this command to copy data from SQL Server into a data file.
Using Data Transformation Services (DTS) DTS is a set of tools provided with SQL Server that makes copying data into and out of SQL Server an easy task. DTS includes a wizard for importing data and a wizard for exporting data.

NOTE
Although staging tables do not provide a method of loading data, they are commonly used in database loading.

Each of these methods has different capabilities and characteristics. You should be able to find at least one method that suits your needs.

NOTE
Recovering a database from a backup file can also be considered a form of database loading, but because database backup and recovery are discussed in Chapters 32 and 33, these topics are not covered here.

Certain database configuration parameters are common to BCP and the BULK INSERT statement. These database parameters define how bulk copies are performed. These settings must be made before any load operation is performed.

You might find these additional actions useful:

The SELECT…INTO statement This statement is used to copy data from one table into another table.
Staging tables Staging tables are temporary tables commonly used to transform data within a database. You can use these tables to facilitate the loading process and to modify data during the loading process.