You work as the database administrator at Domain.com. The Domain.com network consists of a single Active Directory domain named Domain.com. All client computers run Windows XP Professional. The Domain.com network contains a SQL Server 2005 database server named Certkiller -DB01 that hosts a database named CK_Data. The tables in the CK_Data database are shown in the following database diagram.
You create a new database named CK_Staff on Certkiller -DB01. You want to transfer the list of employees from the CK_Data database to the CK_Staff database. You export the Employees table to a text file. The data must be imported as quickly as possible into the following table.
What should you do?
A.
Create an Integration Services task and specify a Copy Column Transformation.
B.
Create a Transform Data task and specify an ActiveX Script transformation.
C.
Create a Transform Data task and specify a Read File transformation.
D.
Create a Bulk Insert task and specify an XML format file.
Explanation:
The best option to import the data as quickly as possible is to use a Bulk Insert task. However, bulk inserts do not support data transformations but a format file can be used with a bulk insert task. You can specify the columns that must be imported, and the order in which they must be imported in the format file.
Incorrect Answers:
A: The Copy Column Transformation is used to transform data from one table as it is being transferred to a new table. It does not copy data from a text file. B, C: SQL Server 2005 does not support Transform Data tasks.
Reference:
Microsoft SQL Server 2005 Books Online (2006), Index: BULK INSERT statement, format files