Lesson 1: Importing and Exporting Data
bcp {[[database_name.][owner].]{table_name | view_name} | "query"}
{in | out | queryout | format} data_file
[-mmax_errors] [-fformat_file] [-x] [-eerr_file]
[-Ffirst_row] [-Llast_row] [-bbatch_size]
[-n] [-c] [-w] [-N] [-V (60 | 65 | 70 | 80)] [-6]
[-q] [-C { ACP | OEM | RAW | code_page } ] [-tfield_term]
[-rrow_term] [-iinput_file] [-ooutput_file] [-apacket_size]
[-Sserver_name[\instance_name]] [-Ulogin_id] [-Ppassword]
[-T] [-v] [-R] [-k] [-E] [-h"hint [,...n]"]
bcp AdventureWorks.HumanResources.Department out c:\test\department.txt -n -SHOTEK –T bcp AdventureWorks.HumanResources.Department in c:\test\department.txt -c -SHOTEK -U<login> -P<password> bcp "SELECT Name, GroupName FROM HumanResources.Department" queryout c:\test\department.txt -n -SHOTEK –T
BULK INSERT [ database_name . [ schema_name ] . | schema_name . ] [ table_name | view_name ] FROM 'data_file' [ WITH ( [ [ , ] BATCHSIZE = batch_size ] [ [ , ] CHECK_CONSTRAINTS ] [ [ , ] CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ] [ [ , ] DATAFILETYPE = { 'char' | 'native'| 'widechar' | 'widenative' } ] [ [ , ] FIELDTERMINATOR = 'field_terminator' ] [ [ , ] FIRSTROW =first_row ] [ [ , ] FIRE_TRIGGERS ] [ [ , ] FORMATFILE = 'format_file_path' ] [ [ , ] KEEPIDENTITY ] [ [ , ] KEEPNULLS ] [ [ , ] KILOBYTES_PER_BATCH =kilobytes_per_batch ] [ [ , ] LASTROW = last_row ] [ [ , ] MAXERRORS = max_errors ] [ [ , ] ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) ] [ [ , ] ROWS_PER_BATCH = rows_per_batch ] [ [ , ] ROWTERMINATOR = 'row_terminator' ] [ [ , ] TABLOCK ] [ [ , ] ERRORFILE = 'file_name' ] )]
Quick Check
1. What are the data formats that BCP supports and the command-line switches for each format?
2. Which parameter do you specify to export data using a query?
3. The Import and Export Wizard is based on which feature of SQL Server?
4. Which sources and destinations is the Import and Export Wizard capable of using?
Quick Check Answers
1. BCP can work with data in either a character or native format. The –c switch
2. The queryout parameter is used to export the result set of a query.
3. The Import and Export Wizard uses a subset of the SSIS feature.
4. You can defi ne any source or destination for which you have an OLE DB provider.
Practice:
bcp AdventureWorks2008R2.HumanResources.Department out c:\test\department.txt -c -SWIN-N1NGJC5BOSR -T bcp AdventureWorks2008R2.HumanResources.Department out c:\test\department.bcp -n -SWIN-N1NGJC5BOSR -T
CREATE DATABASE AdventureWorksTest
Lesson Summary
BCP is a lightweight, command-line utility that allows you to import and export data.
The BCP utility is not designed to provide data transformation or error-handling routines.
In addition to exporting the entire contents of a table or view, you can export the results of a query by using the queryout argument for the BCP utility.
BULK INSERT is a T-SQL command you can use only to import data.
The Import and Export Wizard, based on a subset of SSIS, allows you to move data directly between a source and destination without requiring the use of a fi le.