Bookmark and Share

Friday, May 20, 2011

SQL server BCP in a nutshell

BCP is a sql server utility used for importing and exporting huge quantity of data from a sql server table

You can use BCP without installing sql server, just install Microsoft SQL Server 2008 Command Line Utilities at:

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=b33d2c78-1059-4ce2-b80d-2343c099bcb4

You can Export data from command line:

bcp mydbname.dbo.largetable  out c:\yourtable.csv /U sa /P mypassw /S servername /c

And Import data:

bcp mydbname.dbo.largetable  in c:\yourtable.csv /U sa /P mypassw /S servername /c

For a table with 60 fields the rate import is 10000 records/sec… but this is only a test value depending on the hardware.

You can also export queries using bcp (from msdn):

bcp "SELECT FirstName, LastName FROM AdventureWorks2008R2.Person.Person ORDER BY LastName, Firstname" queryout Contacts.txt -c –T