Using the BCP utility to input and output data to/from Sybase

When performing database maintenance you will occasionally find the need to export data out of your database tables to an operating system for storage, or conversely import data to a table from a file. You may find yourself needing to do these tasks for data backup or for inserting data that comes from a 3rd party export. Sybase makes this process simple by the Bulk Copy Utility (BCP).

Basic BCP Usage

BCP as it’s name suggest is used for 1 purpose the bulk copy of data to and from Sybase Tables. To utilize the utility the basic command format is:

bcp DATABASE..TABLE out C:DBtemp.txt

If you wanted to perform an import you simply switch the out keyword for in.

BCP Option Flags

In addition to the command there are a few options you may also find yourself using.

• Server Flag -S You can specify the Database Server you are accessing by use of the -S flag followed by the Server. (e.g. -S SYBABC )
• User Flag -U If you are not using credentials outlined in your .ini file then you can specify the User you would like to use for authentication using the -U flag followed by the user name. (e.g. -U guest)
• Password Flag -P If no password is supplied then a prompt will occur to input it. You can use the -P flag to specify the password to use to bypass the prompt. (e.g. -P test123)
• Native Format -n To export data in native format which is not readable by looking directly at the file you can use the -n flag. This mode will make it so you don’t have to specify the column format during export.
• Char Format -c To export all data in basic char format then you can use the -c flag. This mode will also make it so you don’t have to specify the column format during export.
• Tab Format -t The default format for delimiting data columns per row is the tab character to specify a different delimiter -t can be used followed by the new delimiter.
• Row Format -r If you want to have a different delimiter then the new line character in your export you can modify it by using the -r flag followed by the new delimiter.

Basic BCP Output Example

bcp DATABASE..TABLE out C:DBtemp.txt -c -S server -U userName -P password

Basic BCP Input Example

bcp DATABASE..TABLE inC:DBtemp.txt -c -S server -U userName -P password

The Bulk Copy Utility (BCP) is a very simple and versatile tool. If you find yourself doing a lot of database management you may want to familiarize yourself with it as it can save you a lot of time, effort, and headaches.

Resources

• Sybase Online Manual – BCP Utility Guide
• MDBitz Project Laboratory
  http://mdbitz.com/2010/04/22/using-the-bcp-utility-to-input-and-output-data-tofrom-sybase/

Syntax BCP Out dengan kondisi tertentu

Perintah BCP dikenal dalam database Sybase berfungsi untuk melakukan copy data dari suatu table. Copy data tabel ini bisa keseluruhan data maupun berdasarkan kondisi query tertentu.
Misalnya saja kita akan melakukan copy data dari data Master CIF dengan kondisi Nasabah yang tahun kelahirannya diatas tahun 1990.
Syntax BCP yang dapat kita jalankan yaitu sebagai berikut :
bcp NamaDatabase.NamaTabel out NamaFile --initstring "select * from NamaDatabase.NamaTabel where kondisi " -c-Usa -Ppassword -SServerName  

Contoh :
bcp DBTEST.MASTER.CIF out CIF1990.TXT --initstring "select * from DBTEST.MASTER.CIF where YEAR(BIRDTHDY) = 1990 " -c-Usa -Ppassword -SServerTest