BCP utility

In this article I will discuss about BCP utility tool

BCP stands for Bulk copy Program which is used to copy bulk data between Microsoft SQL server data table and data file in specified format. This utility mainly used to import/export large number rows between table and file.

The syntax of BCP utility is as follows.

Syntax: BCP {dbtable | query} {in | out | queryout | format} datafile

[-m maxerrors] [-f formatfile] [-e errfile] [-F firstrow] [-L lastrow] [-b batchsize] [-n native type] [-c character type] [-w wide character type] [-N keep non-text native] [-V file format version] [-q quoted identifier] [-C code page specifier] [-t field terminator] [-r row terminator] [-i inputfile] [-o outfile] [-a packetsize][-S server name] [-U username][-P password][-T trusted connection] [-v version] [-R regional enable] [-k keep null values] [-E keep identity values] [-h "load hints"][-x generate xml format file]

Dbtable: the name of the destination table when importing data or source table when exporting data. Dbtable should specify in fully qualified path i.e. (DatabaseName.Schema.tableName)

In: copies data from file to table

Out: copies data from table to file. If you specify existing file, file will be overwritten.

Queryout: copies data into file using query and it should specified only when bulk copying from file

Format: creates format file for the table. The same will be used when bulk copying data using format option. The BCP utility refers this format while inserting data into table which makes sure you need not specify any format information. You need to specify –f option when you are creating format files and also can specify –x if you are creating XML based format file.

Datafile: the name of file. This data file can be source if you are importing data into table or destination when exporting data from table

Remaining all are options mentioned based up on the BCP command you are executing.

Examples:

Let us create below mentioned table

create table FormatExample(id int,

name varchar(10),phonenumber int)

insert into FormatExample values(1,'name1',123456)

insert into FormatExample values(2,'name2',234567)

insert into FormatExample values(3,'name3',345678)

insert into FormatExample values(4,'name4',385678)

insert into FormatExample values(5,'name5',123456)

insert into FormatExample values(6,'name6',345677)

insert into FormatExample values(7,'name7',456788)

insert into FormatExample values(8,'name8',856789)

Importing data from table into file (out will be used)

bcp AdventureWorks2008.dbo.FormatExample out Format.txt -U sa -P <ur_sa password> -c

bcp AdventureWorks2008.dbo.FormatExample out Format.txt -T –c

-c is Char type all columns data will be loaded into text file as character. –T for trusted connection.

Importing data into table from file (in will be used)

bcp AdventureWorks2008.dbo.FormatExample in Format.txt -U sa -P <ur_sa password> -c

bcp AdventureWorks2008.dbo.FormatExample in Format.txt -T –c

Importing data from table into file using query out option

bcp "select id,name,phonenumber from AdventureWorks2008.dbo.FormatExample" queryout Format1.txt -U sa –P<ur_sapassword> -c

This query out option only used when bulk copying data into file.

All above mentioned queries executed in local server as SQL server installed locally. You can even execute same queries on other servers by passing –S option to bcp utility

Creating format file

bcp AdventureWorks2008.dbo.FormatExample format nul -U sa -P <ur_sa password> -n -f Format.fmt(Creates format file in xml format)

bcp AdventureWorks2008.dbo.FormatExample format nul -U sa -P <ur_sa password> -n -x -f Format.xml(Creates format file in XML format).

You should specify nul option after format to create format file.

-c creates all values as character type format file but –n creative native data types in format file.

Importing data with format file:

bcp AdventureWorks2008.dbo.FormatExample in Format.txt -f Format.fmt -U sa -P <ur_sa password> (Import using non-xml format file)

bcp AdventureWorks2008.dbo.FormatExample in Format.txt -f Format.xml -U sa -P <ur_sa password> (Import using xml format file)

NOTE: Please leave comment: