You are the administrator of a SQL Server 2000 computer in your company’s personnel department. Employee data is stored in a SQL

admin2009-05-19  34

问题 You are the administrator of a SQL Server 2000 computer in your company’s personnel department. Employee data is stored in a SQL Server 2000 database. A portion of the database schema is shown in the exhibit.


You want to create a text file that lists these data columns in the following format title, FirstName, LastName, WorkPhone, PositionName, DepartmentName.

You want to create the text file as quickly as possible. You do not expect to re-create this file, and you want to avoid creating new database objects if possible.
What should you do?

选项 A、Use the bcp utility to export data from each table to a separate text file. Use format files to select the appropriate columns. Merge the data from each text file into a single text file.
B、Create a view that joins data from all three tables include only the columns you want to appear in the text file. Use the bcp utility to export data from the view.
C、Create a SELECT query that joins the data from the appropriate columns in the three tables. Add an INTO clause to the query to create a local temporary table. Use the bcp utility to export data from the local temporary table to a text file.
D、Create a SELECT query that joins the data from the appropriate columns in the three tables. Add an INTO clause to the query to create a global temporary table. Use the bcp utility to export data from the global temporary table to a text file.

答案D

解析 Explanation: As the columns that we want in the text file are on different tables in the database, we would have to combine the column into a table or view. Because we will not need to reproduce the file, we can we can combine the column from the various tables into a global temporary table by using the SELECT INTO statement. Then we use the bcp utility to export the data from the global temporary file to a text file.

Note: Temporary tables are not stored in the current database; they are stored in the tempdb system. There are two types of temporary tables: local temporary tables, which are visible only to the connection that created them, and global temporary tables, that are visible to all connections. If global temporary tables are not dropped explicitly before the connection that created them disconnects, they are dropped as soon as all other tasks stop referencing them. No new tasks can reference a global temporary table after the connection that created it disconnects.

The bcp utility copies data between an instance of SQL Server 2000 and a data file in a user-specified format. It can be used to transfer data from a SQL Server table to a data file for use in other programs. Furthermore, data can also be transferred out by specifying a SELECT statement. The bcp utility can use global temporary tables, but not local temporary tables.

Incorrect Answers:
A: Creating a global temporary table by using the SELECT statement and using the bcp utility to export data from that table would require less administrative effort than using bcp to export the required columns from each table into a separate text file and then merging those text files into a single text file.

B: Because we would not need to re-create the data file again, it would be easier to use the SELECT statement to create a global temporary table as these tables are dropped automatically when nothing reference them.

C: The bcp utility cannot use local temporary tables.

Note: Temporary tables are not stored in the current database; they are stored in the tempdb system. There are two types of temporary tables: local temporary tables, which are visible only to the connection that created them, and global temporary tables, that are visible to all connections.
转载请注明原文地址:https://kaotiyun.com/show/QuhZ777K
0

最新回复(0)