Extract & Transform Data with SQL Server 2000
These questions are derived from the Self-Test Software Practice Test for Exam # 70-228 – Installing, Configuring, and Administering Microsoft SQL Server 2000 Enterprise Edition.
Objective: Extracting and Transforming Data with SQL Server 2000
SubObjective: Set up Internet Information Services (IIS) virtual directories to support XML
(Single Answer, Multiple Choice)
You are the SQL administrator for your company. The Human Resources department has decided to migrate its entire 5-GB employee database from an Oracle environment to SQL Server 2000. A Windows 2000 Server computer named HR1 exists that has SQL Server 2000 Enterprise Edition installed.
You create the database and all necessary tables. The data exists in four comma-delimited files, which are to be imported into the SQL Server database.
You want to import the text files into the existing tables in the SQL Server database. You want the method to provide the maximum performance available.
What should you use?
- the bcp utility
- snapshot replication
- a BULK INSERT statement
- a SELECT…INTO statement
- Data Transformation Services (DTS)
C. a BULK INSERT statement
You should use a BULK INSERT statement. The BULK INSERT statement will provide maximum performance. One BULK INSERT script should be created to perform the task. The same script should be used for all four text files.
You should not use the bcp utility. The bcp utility can also be used to import data from a text file. However, this method does not provide the performance that using a BULK INSERT statement does.
You should not use snapshot replication. Snapshot replication is used to copy SQL databases across SQL Server computers. Snapshot replication does not work with text files.
You should not use a SELECT…INTO statement. A SELECT…INTO statement can only be used to retrieve data from an existing table or view. It cannot retrieve data from a text file.
You should not use Data Transformation Services (DTS). Data Transformation Services (DTS) is an easy-to-use tool that makes importing and exporting of data easier than other methods. DTS can be used to import data from sources other than data files. DTS would be the best solution if only one text file needed to be imported. However, when multiple text files must be imported, creating a BULK INSERT statement script is more effective because it can be reused quickly and easily.
BULK INSERT can only be used to insert data into an existing SQL Server database. BULK INSERT is run as a thread within SQL Server.
1. Microsoft SQL Server 2000 Administrator’s Companion – Retrieving Data Using Transact-SQL
– The BULK INSERT Statement
2. Microsoft SQL Server 2000 Administrator’s Companion – Retrieving Data Using Transact-SQL
– Data Transformation Services