Extracting and Transforming Data in SQL Server 2000

by Andrew McLaren.
Share
|
Homepage | Submit your article | Contact | TOS
More articles on databases  

You are here: Categories » Computers and technology » Databases

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
Share
Leave a comment or ask a question
Total comments: 0

Databases Disclaimer

  • The e-articles directory is not responsible for any and all copyright infringements by writers and authors. If you suspect the information contained by this page for any copyright infringements, please contact us to investigate the issue
What is meant by dirty data - Suppose you have leads or contacts in your CRM system that are missing key information, have bounced email addresses, are just plain incorrect, or if you have dupl (more...)
Managing Monitoring and Troubleshooting SQL Server 2000 - SQL Server Agent Jobs SQL Server Agent is a job scheduling agent that aids in the management of a SQL server. SQL Server Agent can be started when the operating system starts (more...)
Managing and Monitoring SQL Server 2000 Security - Authentication There are two methods of authentication offered SQL Server 2000, Windows authentication and SQL Server authentication. Windows authentication allows the SQL server (more...)
Basic Terms of Structured Query Language (SQL) - The first questions to ask are what is SQL and how do you use it with databases? SQL has three main roles: 1. Creating a database and defining its structure 2. Querying the d (more...)
Introduction to SQL - SQL is an acronym for Structured Query Language and is the standard language for interaction with databases. SQL is both an ISO (International Organization for Standardization) and ANSI (Americ (more...)
What Is a Database - A database is simply an organized collection of information. It allows many different types of data to be stored and retrieved in a highly efficient manner. Information within a database (more...)
Installing MySQL Server - Installing MySQL is relatively painless. First, you need the installation program for MySQL, which is available on the companion CD. You can also download the latest version from (more...)
Basic SQL syntax: Creating and Modifying tables and databases - This tutorial shows basic syntax and commands and of the SQL language. Creating and Dropping Databases First, let's see what databases already exist in the MySQL server. We (more...)
Joining SQL Tables - When accessing information within database tables, we may sometimes need to look at data from two or more different tables, as we saw in the previous example. There is another useful way to acc (more...)
Relational SQL Databases - Think of a database that related the players in a game to one another (for example, to determine who was a friend of each player and who was an enemy of each player). First let's create (more...)
 
free content
    Copyright © 2006 - 2012 e-articles.info.
The texts, articles and tutorials in the directory are property of their respective owners and authors.