Extracting and Transforming Data in SQL Server 2000

written by: Andrew McLaren; article published: year 2006, month 08;


In: Root » Computers and technology » Databases » Extracting and Transforming Data in SQL Server 2000

Dutch French Spanish Portuguese Italian German Japanese Chinese Korean Russian Arabic Bookmark and Share this Article

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

Disclaimer

1) E-articles is not responsible for the information contained by this article as well for any and all copyright infringements by authors and writers. E-articles is a free information resource. If you suspect this article for any copyright infringement, please read the terms of service and contact us to investigate the problem.
2) E-articles is not responsible for inaccuracies, falsehoods, or any other types of misinformation this article may contain and will not be liable for any loss or damage suffered by a user through the user's reliance on the information gained here.

link to this article