( Loading tables

Info Catalog ( Creating tables ( Database use ( Retrieving data
 3.3.3 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 that your pet records can be described as shown here.  (Observe
 that MySQL expects dates in `'YYYY-MM-DD'' format; this may be
 different from 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    1979-08-31     1995-07-29
 Chirpy  Gwen    bird    f    1998-09-11     
 WhistlerGwen    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
 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' (backslash, capital-N).  For example, the record for
 Whistler the bird would look like this (where the whitespace between
 values is a single tab character):
 *name*  *owner* *species**sex**birth*        *death*
 `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 '/path/pet.txt' INTO TABLE pet;
 Note that if you created the file on Windows with an editor that uses
 `\r\n' as a line terminator, you should use:
      mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet
          -> LINES TERMINATED BY '\r\n';
 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.
 If the statement fails, it is likely that your MySQL installation does
 not have local file capability enabled by default.  See  `LOAD
 DATA LOCAL' LOAD DATA LOCAL. for information on how to change this.
 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 that 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.
Info Catalog ( Creating tables ( Database use ( Retrieving data
automatically generated byinfo2html