You are a database administrator for a financial firm. You receive data files from other financial firms.
Records inthese input files are delimited with a new line character, and column values are delimited with tab character.
You must validate the data in the input files, and insert the data into multiple tables.
To achieve this, you decide tomove the data to a local temporary table, and then read the data in the temporary table to insert the data into your database tables.
Which statement or tool will best suit your purpose in this scenario?

A.
the bcp utility

B.
the SSIS Import and Export Wizard

C.
the BULK INSERT statement

D.
the SELECT…INTO statement

Explanation:

The BULK INSERT statement is the best option in this scenario. The BULK INSERT statement can be used toread data from a flat file and write the data to a permanent or temporary database table. After the data is copiedinto a temporary table, it can be validated and then moved into multiple tables. The BULK INSERT statementallows you to specify the FIELDTERMINATOR and ROWTERMINATOR arguments to assign the row and columndelimiters for the import process. The bcp utility cannot be used in this scenario because the
bcp utility does not provide any facility for theintermediate processing of data. The bcp utility can be used to directly transfer data from a file into a databasetable or from a database table to a file. However, the bcp utility would not allow you to transfer the data into alocal temporary table for intermediate validation processing. The SSIS Import and Export Wizard cannot be used in this scenario because the SSIS Import and Export Wizardcannot write to temporary tables. The SELECT…INTO statement cannot be used in this scenario because it cannot be used to extract data from aflat file. The SELECT…INTO statement is used to transfer data between two tables in the database or between two databases.

Objective:
Performing Data Management Tasks

Sub-Objective:
Import and export data.

References:
TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Operations > Administration > Importingand Exporting Bulk Data > Scenarios for Bulk Importing and Exporting Data > Exporting Data from or ImportingData to a Temporary Table