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.)