Data Import/Export Architecture
Microsoft® SQL Server™ 2000 has several components that support importing and exporting data:
Data Transformation Services
Data Transformation Services (DTS) can be used to import and export data between heterogeneous OLE DB and ODBC data sources. A DTS package is defined that specifies the source and target OLE DB data sources; the package can then be executed on an as-required basis or at scheduled times or intervals. A single DTS package can cover multiple tables. DTS packages are also not limited to transferring data straight from one table to another, as the package can specify a query as the source of the data. This allows packages to transform data, such as running a query that returns aggregate summary values instead of the raw data.
Replication
Replication is used to create copies of data in separate databases and keep these copies synchronized by replicating modifications in one copy to all the others. If it is acceptable for each site to have data that may be a minute or so out of date, replication allows the distribution of data without the overhead of requiring distributed transactions to ensure all sites have an exact copy of the current data. Replication can therefore support the distribution of data for a relatively low cost in network and computing resources.
Bulk copying
The bulk copy feature of SQL Server allows for the efficient transfer of large amounts of data. Bulk copying transfers data into or out of one table at a time. Bulk copying supports the following bulk copy transfers:
- From one SQL Server table or view to another table or view.
- From a SQL Server table or view into a data file, such as a text file or tab-delimited file.
- The result set of a query into a table, view, or data file.
- The contents of a data file into a table or view.
There are several ways the bulk copy feature can be used:
- The bcp command prompt utility.
- The OLE DB Provider for SQL Server has a provider-specific IRowsetFastLoad interface for bulk copies.
- The SQL Server ODBC Driver supports a set of bulk copy functions.
- The Transact-SQL BULK INSERT statement. This is the fastest of the bulk copy methods. The data file is accessed directly from SQL Server itself, eliminating the overhead of communicating data from a client application to the server.
- The DB-Library API supports a set of bulk copy functions.
Distributed queries
Distributed queries allow Transact-SQL statements to reference data in an OLE DB data source. The OLE DB data sources can be another instance of SQL Server, 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 Server database to an OLE DB data source.
- Import data from an OLE DB data source into SQL Server.