You are the database administrator for Nutex Corporation. The company has a main office and a branch office.Each office contains an instance of SQL Server 2008. You have received a text file named emp.txt from the human resources department in the branch office. The textfile contains information about employees, and the data for each column is separated by a comma. You want toimport the data from the files into a table named employees in a database named hrdb using minimumadministrative effort. Which command should you run?

A.
bcp hrdb.employees in emp.txt ,

B.
bcp hrdb.employees in emp.txt -t ,

C.
bcp hrdb.employees in -f emp.txt -t ,

D.
bcp hrdb.employees in -i emp.txt -t ,

Explanation:

You should run the bcp hrdb.employees in emp.txt -t , command. The bcp utility allows you to copybulk data between a SQL server and a data file in a user-specified format. You can use the bcp command toimport new rows into SQL Server tables or to export data stored in SQL Server tables to data files. In thisscenario, you include the in parameter in the bcp
command. Specifying the in parameter copies data from thespecified file into the table or view. The -t parameter allows you to specify the field terminator, which in thisscenario is a comma. You should not run the bcp hrdb.employees in emp.txt command. The default field terminator used by the bcp utility is the tab character. In this scenario, the data for each column is separated by a comma in the textfile. Therefore, you must specify the -t parameter to specify the comma ( , ) terminator. You should not run the bcp hrdb.employees in -f emp.txt -t , command. This command issyntactically incorrect. You must specify the full path to the data file directly after the in parameter. Also, the -f parameter is used to specify the full path of a format file. A format file stores format information for each field in adata file that is related to a particular table. You can use a format file while bulk importing data into a SQL Servertable or bulk exporting data from a SQL Server table. SQL Server 2005 and SQL Server 2008 support Extended Markup Language (XML) format files in addition tostandard format files. While XML and non-XML format files are interchangeable, it is recommended that you useXML format files because they provide several advantages over non-XML format files. The following is anexample of an XML format file generated from a table named SampleFormatFile by using the bcp utility:
<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="7"/>
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100"COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100"COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="4" xsi:type="CharTerm" TERMINATOR="rn" MAX_LENGTH="100"COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="Col1" xsi:type="SQLSMALLINT"/>
<COLUMN SOURCE="2" NAME="Col2" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="3" NAME="Col3" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="4" NAME="Col4" xsi:type="SQLNVARCHAR"/>
</ROW>
</BCPFORMAT>
bcp Testdb..SampleFormatFile format nul -c -t, -x -f SampleFormatFile.Xml T In this scenario, you already have text files that contain data to be imported into the hrdb database. Therefore,creating a non-XML or XML format file would require additional administrative effort. You should not run the bcp hrdb.employees in -i emp.txt -t , command. This command issyntactically incorrect. You must specify the full path to the data file directly after the in parameter. Also, the -i parameter is used to specify the name of a response file that contains answers for the command promptquestions for each data file when you perform the bulk copy by using interactive mode.

Objective:
Performing Data Management Tasks

Sub-Objective:
Import and export data.

References:
MSDN > MSDN Library > Servers and Enterprise Development > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Technical Reference > Tools Reference >Command Prompt Utilities > bcp Utility
MSDN > MSDN Library > Servers and Enterprise Development > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Operations > Administration > Importingand Exporting Bulk Data > Format Files for Importing or Exporting Data > Introduction to Format Files
Page 226 of 226Copyright 2011 Transcender LLC, a Kaplan Professional Company. All Rights Reserved.