CAD/MCSE/MCDBA Self-Paced Training Kit [Electronic resources]: Microsoft SQL Server 2000 Database Design and Implementation, Second Edition (Exam 70-229)

Microsoft Corporation

نسخه متنی -صفحه : 223/ 121

Objective 3.1: Import and export data.

Three main methods are provided by SQL Server 2000 to import and export data: the BCP utility, the BULK INSERT Transact-SQL command, and DTS.

The BCP utility is a fast method to import data from a file to a SQL Server table or view, or to export data from a SQL Server table, query, or view to a file. The BCP utility is the best choice when data is being moved from or to a file, and the move needs to be fast, with no transformations or other manipulations of any kind. The BCP operations might be logged, depending on a number of factors.

The BULK INSERT command is a regular Transact-SQL statement, much like the INSERT command, but it is used to insert data from a data file into a SQL Server table or view. BULK INSERT is significantly faster than BCP or DTS for text file imports. However, when importing native data, BCP performance is comparable to BULK INSERT performance. One of the main advantages of BULK INSERT is that it can be used within Transact-SQL and can be used within a transaction, allowing for rollback if needed.

DTS provides a very flexible and powerful framework for moving and transforming data and database objects. It has a number of wizards and tasks built in for importing and exporting data from multiple ODBC and OLE DB data sources. It provides a GUI to create complex workflows that can be saved and scheduled to be executed at another time. DTS allows data transformations to be included in the data movement tasks. These transformations can be written in VBScript or Microsoft JScript.

DTS exposes a COM object model that can be used with programming languages such as Microsoft Visual C++ or Microsoft Visual Basic to write more complex data transformation tasks within a program. DTS supplies an OLE DB provider called the DTS Data Pump, which exposes the interfaces and methods to move and transform data.

It is important to choose the appropriate tool for each data transfer need, to ensure flexibility as well as performance. However, with the three tools provided, there is very little that you cannot accomplish in data transfer.

Objective 3.1 Questions

70-229.03.01.001

You are about to bulk load a BCP export file into your database. You want the data to be loaded as quickly as possible. Which of the following must be met to ensure that your BCP load is as fast as possible?

Enable Select into/bulk copy.

The table cannot have an index.

The table can be replicated.

The filegroup of the database cannot have more than one file.

70-229.03.01.002

You need to import about a thousand rows of data from another system into a table on the production system. You want to be able to do this within a transaction that can be rolled back, if needed. This data must also be fully logged, and you need to ensure that all referential integrity on the table is checked, whether in constraints or triggers.

Which of the following approaches will best meet these requirements?

Use the BCP command-line utility to bulk copy the data with the FIRE_TRIGGERS and CHECK_CONSTRAINTS options.

Use the BCP command-line utility to bulk copy the data after setting the Select into/bulk copy database option.

Use the BULK INSERT command within a transaction with the FIRE_TRIGGERS and CHECK_CONSTRAINTS options.

Use the BULK INSERT command with the TABLOCK option.

70-229.03.01.003

Which of the following functions can be performed using DTS? (Choose all that apply.)

Move data between a SQL Server table and a Microsoft Access table, converting numeric data to character data.

Use File Transfer Protocol (FTP) to copy data from one location to another.

Use Message Queuing to split a large DTS job into smaller pieces and send the tasks to multiple computers.

Transfer logins and user-defined error messages from one server to another.

70-229.03.01.004

You are about to use DTS to load data from an Oracle database using Microsoft OLE-DB Provider for Oracle. What operations are valid? (Choose all that apply.)

Change the column data types.

Specify a query to retrieve a subset of the Oracle data.

Use JScript to write transformation scripts.

The destination table must be dropped and re-created before the data can be imported.

70-229.03.01.005

You want to export data from SQL Server to a text file. Which utilities can you use? (Choose all that apply.)

Use ISQL.

Use the DTS Export Wizard.

Use BCP.

Answers

A. This option must be enabled on your database to allow fast data load; however, the load has to fulfill other requirements. If you want all of your databases to have this option enabled when they are created, enable this option on your model database.

C. BULK INSERT is a Transact-SQL command that can be run within a transaction and rolled back if needed. The FIRE_TRIGGERS options will force the INSERT TABLE triggers to fire, which by default are not fired. The CHECK_CONSTRAINTS option forces constraints to be checked, which are also ignored by default. Also, the FIRE_TRIGGERS option ensures that the operation is fully logged.

A. With DTS, Data can be moved between SQL Server and Access.

B. The built-in DTS File Transfer Protocol Task will allow files to be copied from one location to another, as well as entire directories.

C. Message Queuing can be used to send and receive messages between DTS packages for asynchronous processing. Messages can be received by multiple computers to perform different tasks. However, Message Queuing cannot take part in a DTS transaction.

D. The built-in DTS Transfer Logins Task can be used to transfer logins from one system to another, and the built-in Transfer Error Messages Task will transfer any error messages that have been added to the system with the sp_addmessage system stored procedure.

A. You can convert from almost any SQL Server data type to any other data type. If simple conversion is not available, you can always write a script to transform the data.

B. If the data source driver or database supports SQL, you can always specify a SELECT statement to retrieve data.

C. SQL Server allows you to use VBScript or JScript to write your data transformation script.

B. SQL Server includes a service called Data Transformation Services (DTS). DTS is used to transfer data to and from SQL Server from different data sources, such as ODBC, OLE DB, or text files. DTS allows you to specify the data you want to export and the format. You can also save the job in a package and reuse it at a later time.

C. BCP is a command-line utility used to import and export data in a native SQL Server format or in text format. BCP is used to transfer large amounts of data. BCP uses the ODBC API Bulk Copy interface, whereas earlier versions of BCP used DB-Library's bulk copy API.