bcp command in sybase

bcp: The bulk copy program(bcp) is command line utility which copies data from a table/view to OS file or from OS file to a table/view.

  • If we are copying data from a OS file to a table/view, the data is appended to existing data in the table.
  • If we we copying data from a table/view, then the existing data in the OS file is overwritten.
  • There are two types bcp modes. They are slow and fast bcps.
  • BCP file locations:

Windows Operating Systems:: %SYBASE%/%SYBASE_OCS/bin/bcp.exe
Unix Operating Systems: #SYBASE/$SYBASE_OCS/bin/bcp

Syntax:

bcp database_name..table_name [in/out] [options]

Options

Option Description
-S Name of the server to connect
-U User name used to connect to the server
-P Password of user to connect to the shadow
-C Character format
-n Native format
-i Input files
-o output file
-t column terminator
-r row terminator
-e Error file

bcp modes
bcp modes are slow bcp and fast bcp. Slow bcp logs every row inserted. Fast bcp only logs the allocation of new extents. Fast bcp makes for a much speedier process, fact bcp is not as recoverable in the event of interruption or failure.

Before performing fast bcp, We must ensure the following, if below conditions are not met, then bcp runs slow bcp.

  • The database options “select into/bulkcopy/pllsort” is set to true.
  • Triggers does not exist on the table
  • Indexes does not exist on the table.
  • If table is large and includes indexes and triggers, then drop indexes and triggers. After dropping indexes and triggers, then load the data into the table using bcp. After bcp completion, re-create the indexes and triggers.

Examples:

1. bcp out using the options -c, -t, -r

bcp pubs2..publishers out pub_back.out -Uuser_name -Sserver_name -Ppasswd -c -t ‘\t’ -r ‘\n’

2. bcp in table using data file, that was bcp out in the above example

bcp pubs..publishers in pub_back.out -Uuser_name -Sserver_name -Ppasswd

Responses are currently closed, but you can trackback from your own site.

Comments are closed.

Powered by k2schools
%d bloggers like this: