BCP -Bulk Copy Protocol

BCP (Bulk Copy protocol)

• Copying bulk data from one table/server to another table/server using a protocol is called bulk copy protocol
• BCP is used to copy the contents of a table into a flat file & vice verse

There are two types of BCP :

FAST BCP

In this the DB option “select into bulkcopy/pllsort” is Enabled and there are no triggers or indexes on the tables such type of bcp is called as FAST BCP

• FAST BCP is minimally logged operation
• When you do a fast bcp it will not allow the transaction dumps to happen

SLOW BCP

When “select into bulkcopy/pllsort” is NOT-Enabled and there are triggers or indexes on the tables such type of bcp is called as SLOW BCP

• Slow BCP is fully logged operation

Advantages :

• It is used to transfer tables data from one database to another
• To copy data into or out of a table
• To copy data from one server to another
• To copy data out of view
• Used to copy large amounts of data from table to Operating system files and vice-verse

BCP OUT

• BCP OUT is used to take the contents of a table view to flat file
• we can take the table view data into a flat file

Syntax:

bcp <database_name>..<table_name> OUT <Flnme.txt>-Usa -S<srvnm> -P<pwd>

-c → used for character

-t → used to specify the field terminator eg: -c -t “|”

-F → first row of the table

-L → last row of the table

-b → used for batch size (default size is 1000;Adaptive Server copies all the rows in batches of 1000 lines)

-n → native format (only native to that operating system )

*** we can BCP OUT from a view

BCP IN

• BCP IN is used to copy the content of a flat file into a table
• while BCP IN is used to copy the contents of a file (flat file) into a table

Syntax:

bcp <Target_db_name>..<table_name> IN <Flnme.txt> -Usa -S<srvnm> -c -t “|”-b 1000

*** BCP OUT can be done from view but BCP IN cannot be done into a view

*** The trigger will no be fixed while doing a BCP IN on a table which has trigger enabled

Requirement:

The structure of the tables should same

Example: Suppose in a database db1 we have a table student with following structure

Sid|Sname|marks|Age|Address

we have some data in the student table .Now we want the same data in the database db2 means we have to copy the data of db1 student table to db2

In this case we use bcp, to do bcp there should be a table exists in db2 with the same structure as in db1 the table name can differ

Syntax :bcp database_name.owner.table_name out filename -U-S

this has to be done on UNIX level not server level i.e NOT connecting to ISQL

Example :bcp db1..student out std.txt -c-Usa -Sxyzserver -Ppassword

For each copy you must specify

• The name of the database ,tables,views
• The name of the operating system
• The direction of transfer (IN or OUT)