Saturday, April 14, 2012

Sybase: How to BCP data in and out of databases

To quickly copy data from a table in one database to another database, for example, from production to a development environment, use the Sybase bcp utility as follows:

Step 1: bcp out to a file
First run bcp to copy data out of your database table and into a flat file. Just hit [Return] when prompted for lengths of columns, but remember to save the table format information to a file. An example is shown below:

$ bcp  Customers out /tmp/bcp.out -S server1 -t, -U username -P password
Enter the file storage type of field firstName [char]:
Enter prefix-length of field firstName [0]:
Enter length of field firstName [32]:
Enter field terminator [,]:

Enter the file storage type of field lastName [char]:
Enter prefix-length of field lastName [0]:
Enter length of field lastName [10]:
Enter field terminator [,]:

Enter the file storage type of field accessTime [smalldatetime]:
Enter prefix-length of field accessTime [0]:
Enter field terminator [,]:

Do you want to save this format information in a file? [Y/n] Y

Host filename [bcp.fmt]: /tmp/bcp.fmt

Starting copy...

14 rows copied.
Clock Time (ms.): total = 1  Avg = 0 (14000.00 rows per sec.)
Step 2: bcp in to the target database
Next run bcp to copy data from the flat file to your target database using the format file you saved in Step 1.
$ bcp  Customers in /tmp/bcp.out -S server2 -f /tmp/bcp.fmt -U username -P password
Starting copy...

14 rows copied.
Clock Time (ms.): total = 9  Avg = 0 (1555.56 rows per sec.)

4 comments:

  1. Where do I get bcp?

    I know Microsoft has a bcp utility but it's only for MSSQL, right?

    ReplyDelete
  2. Very well blog content i found here. I would like to thanks for this great stuff.

    galvanized garbage can

    ReplyDelete
    Replies
    1. This is a simpler version of what sybooks offers.
      http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc30191.1570100/doc/html/san1367605030412.html

      Delete
  3. Thanks a lot for such a wonderful post, the stuff posted were really interesting and useful. The quality of the content was good and clear. Thanks for the post

    outdoor garbage containers

    ReplyDelete