Microsoft SQL Server 7.0 System Administration Training Kit
You can import and export data from SQL Server with several tools and Transact-SQL statements. Additionally, with the programming models and application programming interfaces (APIs) that are available with SQL Server, such as the Data Transformation Services object model, you can write your own programs to import and export data. This lesson provides an overview of the data transfer process and describes the various tools you can use to transfer data in SQL Server 7.
After this lesson, you will be able to
- Describe the rationale for, and the process of, transferring and transforming data
- Describe the tools for transferring data in SQL Server version 7
Estimated lesson time: 45 minutes
Why Transfer Data?
As a system administrator, you must understand how to manage data and transfer it between applications and environments. Almost all environments require some degree of data transfer for one or more of the following reasons:
- To move data to another server or location
- To make a copy of data
- To archive data
- To migrate data
The process of copying data from one environment to another typically involves
- Identifying the data source
- Specifying the data destination
- Manipulating or transforming the data between the source and destination (optional)
Simple importing and exporting of data is the most basic form of data transfer. Even this simple process can transform data if, for example, you specify a different data type for a column or save a file in another product version or format.
A SQL Server administrator frequently needs to transfer data between heterogeneous environments. For example, you might transfer sales information from an Oracle database to a SQL Server database or transfer data from an online transaction processing system to a data warehouse.
Why Transform Data?
Migrating and transferring data between different environments is a common occurrence that often involves the manipulation or transformation of data. Transforming data can be as simple as mapping transformation data types or as complex as programming data logic to handle data transformations.
During data transformation, missing values can be added and column values summarized, decoded, decomposed, converted, and translated to a common measure or format. The captured data typically is integrated, made consistent, validated, and restructured before it is stored at the destination.
When you transform data, you may want to do one or more of the following:
Change the Format of Data
Transforming data frequently requires changing its format. Suppose, for example, that a value of 1 or 0 is stored in the Active_Customer column in your database, but the data that you want to transfer into your database represents the value as the text 'true' or 'false'. You can convert the 'true' and 'false' values to 1 and 0 values when you transfer the data into your database. Numeric and date formats are frequently changed.
Restructure and Map Data
Restructuring and mapping data frequently involves combining data from multiple data sources, tables, and columns into a single data set at the destination. For example, you can preprocess the data (this is known as data aggregation or summarization) and store the preprocessed data at your destination.
Make Data Consistent
When you import data from another source, you should make sure that the new data is consistent with the existing data. This is sometimes called data scrubbing. Data can be inconsistent in several ways:
- The data is consistent, but the representation is not consistent with how you want to store it at the destination. For example, suppose that a credit rating is represented by the values 1, 2, and 3. Making the data consistent may require translating these values to the character string values of 'Good', 'Average', and 'Poor'.
- The data representation is correct, but it is inconsistently represented. For example, a company name may be stored in several ways, such as ABC Corp., ABC, or ABC Corporation. In this instance, you can make the data consistent by requiring that the destination always store the company name as ABC Corporation.
You generally can make your data consistent by translating codes or values to readable strings or by converting mixed values to single values.
Validate Data
When you validate data, you verify the accuracy and correctness of the data that you import. For example, you can require that data meet a specific condition before it can be included with your destination data. Or you can verify that a customer ID already exists at the destination before you transfer additional information for the customer into the destination data.
If you discover any invalid data, try to determine where the fault originated and correct the processes that are contributing to the error. Save invalid data to a log for later examination to determine why it is incorrect.
Tools for Transferring Data in SQL Server
SQL Server provides several tools and Transact-SQL statements for transferring data. The data that you can transfer is typically in the form of tables or files. The method you choose for importing or exporting data depends on a variety of user requirements, including
- The format of the source and destination data
- The location of the source and destination data
- Whether the import or export is a one-time occurrence or an ongoing task
- Whether a command-prompt utility, Transact-SQL statement, or graphical interface is preferred (for ease of use)
- The type of import or export operation
The following table describes the tools that SQL Server provides for transferring data.
| Tool | Description | Use |
|---|---|---|
| DTS Import Wizard and DTS Export Wizard | Allow users to interactively create DTS packages that can be used to import, export, and transform data. | Transferring data between heterogeneous data sources or transferring all of the objects in a SQL Server 7 database to another SQL Server 7 database. |
| DTS Designer | Allows experienced database administrators to import, export, and transform data and define complex data workflows. | Transferring homogeneous and heterogeneous data from multiple sources and for setting up complex workflows. |
| dtsrun utility | A command-prompt utility that allows you to execute existing DTS packages from a command prompt. | Executing a DTS package as part of a batch or scheduled job. |
| Bulk copy program (bcp utility) | A command-prompt utility that imports and exports native SQL Server data files or ASCII text files. | Importing data into a SQL Server table from a file or exporting data from a SQL Server table to a file. |
| Transact-SQL statement | SELECT INTO and INSERT SELECT. | Selecting data to add to a table from an existing SQL Server table - SELECT INTO creates a new table, and INSERT SELECT requires an existing table. |
| BULK INSERT. | Copying a data file into a database table in a user-specified format. The fastest method of loading large amounts of data into a table. | |
| BACKUP and RESTORE. | Copying a complete SQL Server database (all data and objects) to another SQL Server. | |
| sp_attach_db | Attaches a database to a server. | Moving or copying a complete SQL Server database (all data and objects) to another SQL Server by copying the database files. |
| Replication | Maintains duplicate table schema, data, or stored procedure definitions from a source database to a destination database, usually on separate servers. | Maintaining copies of data intermittently on multiple databases (does not guarantee that the data will be consistent at the same point in time). An ongoing process. |
| Host Data Replicator | Provides replication to and from SQL Server and mainframe databases such as IBM DB2. Runs in conjunction with Microsoft SNA Server, a gateway and application integration platform that is part of BackOffice. Also supports data transformations such as conversion of date and timestamps. Data can replace existing tables or be merged on a row-by-row basis. | Transferring data between SQL Server and mainframe databases such as IBM DB2. |
NOTE
DTS is not intended to replace SQL Server replication. Replication uses a store-and-forward database to capture changes in one location and then forward them to multiple destinations. Replication captures changes from relational data sources and normally provides very little data cleansing and transformation capabilities. Using DTS rather than replication requires executing a complete transformation for each destination.
NOTE
The SQL Server Transfer Manager found in previous versions of SQL Server is no longer available. DTS provides all of the functionality formerly provided by the SQL Server Transfer Manager.
Tip
To migrate data from SQL Server 6.5 to SQL Server 7, use the SQL Server Upgrade Wizard, as described in Chapter 3, "Upgrading to SQL Server 7.0"
Exercise: Enabling Bulk Copy
In this exercise, you will set the appropriate options in the StudyNwind database to enable fast bulk copy.
NOTE
You will use the StudyNwind database for the first time in this exercise. StudyNwind is a copy of the Northwind sample database that is installed with SQL Server. You must use the C:\Sqladmin\Exercise\Setup\Maknwind.cmd batch file to create the StudyNwind database. You can re-create StudyNwind as often as you like by executing the batch file again during the course, so you are free to change the data without worrying about making the database unusable.
- To configure the StudyNwind database for bulk copy
- Start SQL Server Enterprise Manager.
- Expand your server, and expand Databases.
- Right-click the StudyNwind database icon, and then click Properties.
- Click the Options tab and verify that the Select Into/Bulk Copy option is checked. If it is not, check it and then click OK.
Exercise: Importing Data with the bcp Utility
In this exercise, you will create a batch file that uses the bcp utility to import more than 1000 records from a tab-delimited text file into the Products table in the StudyNwind database. C:\Sqladmin\Exercise\Ch06\Runbcpa.cmd is a completed batch file for this exercise.
- To import data using the bcp utility
- Open Notepad and type the following bcp command. An explanation of the parameters is given in the following table.
- Save the file with the name Runbcp.cmd in the C:\Sqladmin\Exercise\Ch06 folder.
- From a command prompt, execute the C:\Sqladmin\Exercise\Ch06\Runbcp.cmd file. You are prompted for a password. Enter the password for the sa login and press Enter, or just press Enter if your sa password is blank.
- In Notepad, review the output from the C:\Sqladmin\Exercise\Ch06\Newprods.err error file.
- Switch to SQL Server Enterprise Manager.
- Right-click the StudyNwind database, and then click Properties.
- In the Properties dialog box, click the Options tab, clear the Select Into/Bulk Copy check box, and then click OK.
bcp StudyNwind..Products in C:\Sqladmin\Exercise\Ch06\Newprods.txt /c /t"," /r\n /e C:\Sqladmin\Exercise\Ch06\Newprods.err /b250 /m50 /SSQLSERVER /Usa
IMPORTANT
You must enter the command as a single line (do not insert any hard returns). The command syntax for the bcp utility must include only one line of information. The arguments are case sensitive. Replace the server name with your server name. For more information, search for "bcp utility" in SQL Server Books Online.
An explanation of the parameters is given in the following table.
| Parameter | Value |
|---|---|
| Database and table | StudyNwind..Products |
| Data direction | In |
| Transfer file | C:\Sqladmin\Exercise\Ch06\Newprods.txt |
| Data: character only | /c |
| Field terminator: comma | /t"," |
| Row terminator: new line | /r\n |
| Error file | /e C:\Sqladmin\Exercise\Ch06\Newprods.err |
| Batch size | /b250 |
| Maximum errors | /m50 |
| Server name (replace SQLSERVER with your server name if your server is not called SQLSERVER) | /SSQLSERVER |
| Username | /Usa |
How many rows were copied?
Answer
Did any errors occur?
Answer
Lesson Summary
This lesson provided an overview of the data transfer process and described the various tools you can use to transfer data in SQL 7. You can import and export data from SQL Server with several tools and Transact-SQL statements. Additionally, with the programming models and APIs that are available with SQL Server, such as the DTS object model, you can write your own programs to import and export data.
The method you choose for importing or exporting data depends on a variety of user requirements, including the format of the data, the location of the data, how often the transfer will be occurring, the type of import or export, and finally, ease of use.