Populating Your Database in Sybase

Now that you have successfully created your relations in Sybase, you will want to populate your database with tuples. You can add tuples one at a time using SQL insert commands, but to load your database with significant amounts of data you will want to use Sybase's ``bulk loading'' facility as follows.

Create all your tuples in Unix human readable (ASCII) files. For Assignment #3 you will first create sample tuples using a text editor (such as emacs or vi) to type in your data. Use one file for each relation and one line for each tuple. These files should contain only data, no Sybase or SQL commands. After experimenting with loading small amounts of hand-created data, you will write a program to generate large amounts of data in the same load file format.
Load your files into your Sybase database. Sybase has a facility called bcp that allows you to bulk load your ASCII data files into your database. For each relation you want to populate, you need to invoke bcp separately. bcp is a Unix command. For instance:
```
     bcp NURSE in nurses.txt -U mary -P MYPW -c -t , -r \\n
```
copies into the preexisting relation NURSE the tuples specified in the data file nurses.txt. The user here is mary with Sybase password MYPW. The ``-c'' option means the data file is in ASCII characters, ``-t ,'' means components within a tuple are separated by the ``,'' character, and ``-r \\n'' means each tuple in your data file is terminated by a newline character. For more information on bcp, use sybooks or the syman command.
Once you've loaded your data, you can examine the contents of a relation whose name is REL-NAME by invoking sqsh. The SQL command (issued from sqsh) is:
```
     select * from REL-NAME
     \go
```
You can delete all tuples in a relation (but not the relation itself) by invoking the Sybase SQL command:
```
     delete from REL-NAME
     \go
```

Example

The following is a trace that illustrates the creation of a relation, bulk loading a data file into the database, checking the relation, and deleting all tuples from the relation.

elaine35:~> cat nursecreate
create table NURSE
   (EmpID int,
    SSN char(12),
    Name char(20))

\go
elaine35:~> cat nurses.txt
101, 415-49-1234, Gina
48, 618-37-9876, Jane
2, 911-68-1357, Jill
elaine35:~> sqsh -U mary -P MYPW < nursecreate
elaine35:~> bcp NURSE in nurses.txt -U mary -P MYPW -c -t , -r \\n

Starting copy...

3 rows copied.
Clock Time (ms.): total = 1      Avg = 0      (3000.00 rows per sec.)
elaine35:~> sqsh -U mary -P MYPW
sqsh-1.2 Copyright (C) 1995, 1996 Scott C. Gray
This is free software with ABSOLUTELY NO WARRANTY
For more information type '\warranty'
1> select * from NURSE
2> \go
 EmpID       SSN          Name                 
 ----------- ------------ -------------------- 
         101  415-49-1234  Gina                
          48  618-37-9876  Jane                
           2  911-68-1357  Jill                

(3 rows affected)
1> delete from NURSE
2> \go
(3 rows affected)
1> select * from NURSE
2> \go
 EmpID       SSN          Name                 
 ----------- ------------ -------------------- 

(0 rows affected)
1> \quit
elaine35:~>

The TAs of CS145, cs145ta@cs.stanford.edu, last modified: 10/15/96