GNU Info

(mysql.info)Loading tables

---

Next: Retrieving data Prev: Creating tables Up: Database use

---

Enter node , (file) or (file)node

Loading Data into a Table
-------------------------

After creating your table, you need to populate it.  The `LOAD DATA' and
`INSERT' statements are useful for this.

Suppose your pet records can be described as shown below.  (Observe
that MySQL expects dates in `YYYY-MM-DD' format; this may be different
than what you are used to.)

*name*       *owner*      *species*    *sex*        *birth*      *death*
Fluffy       Harold       cat          f            1993-02-04   
Claws        Gwen         cat          m            1994-03-17   
Buffy        Harold       dog          f            1989-05-13   
Fang         Benny        dog          m            1990-08-27   
Bowser       Diane        dog          m            1998-08-31   1995-07-29
Chirpy       Gwen         bird         f            1998-09-11   
Whistler     Gwen         bird                      1997-12-09   
Slim         Benny        snake        m            1996-04-29   

Because you are beginning with an empty table, an easy way to populate
it is to create a text file containing a row for each of your animals,
then load the contents of the file into the table with a single
statement.

You could create a text file `pet.txt' containing one record per line,
with values separated by tabs, and given in the order in which the
columns were listed in the `CREATE TABLE' statement.  For missing
values (such as unknown sexes or death dates for animals that are still
living), you can use `NULL' values.  To represent these in your text
file, use `\N'.  For example, the record for Whistler the bird would
look like this (where the whitespace between values is a single tab
character):

`Whistler'  `Gwen'      `bird'      `\N'        `1997-12-09'       `\N'

To load the text file `pet.txt' into the `pet' table, use this command:

     mysql> LOAD DATA LOCAL INFILE "pet.txt" INTO TABLE pet;

You can specify the column value separator and end of line marker
explicitly in the `LOAD DATA' statement if you wish, but the defaults
are tab and linefeed.  These are sufficient for the statement to read
the file `pet.txt' properly.

When you want to add new records one at a time, the `INSERT' statement
is useful.  In its simplest form, you supply values for each column, in
the order in which the columns were listed in the `CREATE TABLE'
statement.  Suppose Diane gets a new hamster named Puffball.  You could
add a new record using an `INSERT' statement like this:

     mysql> INSERT INTO pet
         -> VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);

Note that string and date values are specified as quoted strings here.
Also, with `INSERT', you can insert `NULL' directly to represent a
missing value.  You do not use `\N' like you do with `LOAD DATA'.

From this example, you should be able to see that there would be a lot
more typing involved to load your records initially using several
`INSERT' statements rather than a single `LOAD DATA' statement.