Join Us!

Spaces are allowed; punctuation is not allowed except for periods, hyphens, and underscores.
A valid e-mail address. All e-mails from the system will be sent to this address. The e-mail address is not made public and will only be used if you wish to receive a new password or wish to receive certain news or notifications by e-mail.
CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
Image CAPTCHA
Enter the characters shown in the image.

Login

Enter your Project Envision username.
Enter the password that accompanies your username.
Request new password

Sql Server Exporting Data with BCP

BCP is one of the oldest tools you can find in current version of Sql Server, Microsoft started releasing it with early versions of Sql Server. It is designed to perform fast import and export operations. BCP is considered as a lightweight solution for import and export operations, if you need to perform transformations or error handling you should use Sql Server Integration Services (SSIS) instead. This blog post will present the basic ways how to export data from Sql Server database using BCP.

BCP allows you to export data from tables and views. User performing export only requires SELECT permission on relevant table or view. BCP can export data in two formats one is internal SQL Server format and to use it you must specify –n as an option, the second is character format which is specified by –c switch. “-n” and “-c” options are mutually exclusive. To perform an export in native SQL format of table Departments from HumanResources schema, we can use following command

C:\>bcp AdventureWorks.HumanResources.Department out person.txt -n -S SQL1 -T

Starting copy...

16 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 15 Average : (1066.67 rows per sec.)

When you will open person.txt file apart from standard text you will see some strange characters, which were put there because we specified –n switch. To export the same table in character format we need to use –c switch.

C:\>bcp AdventureWorks.HumanResources.Department out person1.txt -c -S SQL1 -T

Starting copy...

16 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 15 Average : (1066.67 rows per sec.)

When you will open person1.txt you will see only standard characters. The other switches I used in above examples are “out” which specifies the out file for export –S switch is used to specify the Sql Server, -T means that I want to connect using trusted connection. Instead of using –T, you can use –U and –P to provide username and password.

One of the handy features of bcp is that it allows you to specify a query upon which you will select data to export instead of exporting whole view or table. Below example show how to export all employees that are men.

C:\>bcp "select * from AdventureWorks.HumanResources.Employee where Gender = 'M' 
" queryout maleemp.txt -c -S SQL1 -T

Starting copy...

206 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 31 Average : (6645.16 rows per sec.)

The –n switch usually offers better performance than –c switch, however it does not allow you to move the file between database platforms.