SQL Server 2000 has several components that support the import and export of data.

Data Transformation Services (DTS) is used to import and export data between like OLE DB and ODBC sources. A DTS package is defined that specifies the source and target data sources which can then be executed on an ad hoc basis or scheduled to run at predefined times. DTS packages can also specify a query as the source of the data which allows packages to transform data by performing an action like running a query that returns aggregate summary values instead of the raw data.

Replication is used to store synchronized copies of data in separate databases to maintain data recovery integrity. Replication minimizes the overhead required to ensure that all servers have exact copies of current data by removing the need for distributed transactions. In replication there are two parties, the publisher and the subscriber. The publisher sends replication data and the subscriber receives the replication data. There are three types of replication:

• 1. Snapshot replication: all data from a publishing site is copied to all subscribing sites and individual changes are not replicated.
• 2. Transactional replication: One server is a publisher and other servers subscribe to articles that are published. An article is an individual set of data to be replicated. All changes to the data are made at the publisher’s server and are then replicated to the subscribers.
• 3. Merge replication: the publisher specifies the articles to be published and all participating parties can make changes. System tables keep track of changes and propagate them to all participating servers. There are two types of subscriptions, push and pull. Push subscriptions require the publisher to be responsible for the transfer of updates to subscribers. Pull subscriptions allow subscribers to initiate replication.

Bulk Copy - The bulk copy feature is a method for the efficient transfer of large amounts of data. Bulk copy operations transfer data into or out of one table at a time. Bulk copying supports the following transfers:

• From one table or view to another table or view
• From a table or view to a data file
• Query results into a table, view, or data file
• From a data file into a table or view

Distributed Queries allow Transact-SQL statements to reference data from a data source. These data sources can be another server’s database, or a heterogeneous data source such as Microsoft Access or Oracle. SELECT INTO and INSERT statements can be used to:

• Export data from a SQL database to an OLE DB data source
• Import data from an OLE DB data source