<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="loading-tables">
      </a>
      5.3.3 Loading Data into a Table
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045325408192">
  </a>
  <a class="indexterm" name="idm46045325406736">
  </a>
  <a class="indexterm" name="idm46045325405248">
  </a>
  <p>
   After creating your table, you need to populate it. The
   <a class="link" href="load-data.html" title="15.2.9 LOAD DATA Statement">
    <code class="literal">
     LOAD DATA
    </code>
   </a>
   and
   <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
    <code class="literal">
     INSERT
    </code>
   </a>
   statements are useful for
        this.
  </p>
  <p>
   Suppose that your pet records can be described as shown here.
        (Observe that MySQL expects dates in
   <code class="literal">
    '
    <em class="replaceable">
     <code>
      YYYY-MM-DD
     </code>
    </em>
    '
   </code>
   format; this may differ from what you are used to.)
  </p>
  <div class="informaltable">
   <table summary="Example of pet records mentioned in the preceding text.">
    <colgroup>
     <col style="width: 10%"/>
     <col style="width: 10%"/>
     <col style="width: 10%"/>
     <col style="width: 05%"/>
     <col style="width: 20%"/>
     <col style="width: 20%"/>
    </colgroup>
    <thead>
     <tr>
      <th scope="col">
       name
      </th>
      <th scope="col">
       owner
      </th>
      <th scope="col">
       species
      </th>
      <th scope="col">
       sex
      </th>
      <th scope="col">
       birth
      </th>
      <th scope="col">
       death
      </th>
     </tr>
    </thead>
    <tbody>
     <tr>
      <th scope="row">
       Fluffy
      </th>
      <td>
       Harold
      </td>
      <td>
       cat
      </td>
      <td>
       f
      </td>
      <td>
       1993-02-04
      </td>
      <td>
      </td>
     </tr>
     <tr>
      <th scope="row">
       Claws
      </th>
      <td>
       Gwen
      </td>
      <td>
       cat
      </td>
      <td>
       m
      </td>
      <td>
       1994-03-17
      </td>
      <td>
      </td>
     </tr>
     <tr>
      <th scope="row">
       Buffy
      </th>
      <td>
       Harold
      </td>
      <td>
       dog
      </td>
      <td>
       f
      </td>
      <td>
       1989-05-13
      </td>
      <td>
      </td>
     </tr>
     <tr>
      <th scope="row">
       Fang
      </th>
      <td>
       Benny
      </td>
      <td>
       dog
      </td>
      <td>
       m
      </td>
      <td>
       1990-08-27
      </td>
      <td>
      </td>
     </tr>
     <tr>
      <th scope="row">
       Bowser
      </th>
      <td>
       Diane
      </td>
      <td>
       dog
      </td>
      <td>
       m
      </td>
      <td>
       1979-08-31
      </td>
      <td>
       1995-07-29
      </td>
     </tr>
     <tr>
      <th scope="row">
       Chirpy
      </th>
      <td>
       Gwen
      </td>
      <td>
       bird
      </td>
      <td>
       f
      </td>
      <td>
       1998-09-11
      </td>
      <td>
      </td>
     </tr>
     <tr>
      <th scope="row">
       Whistler
      </th>
      <td>
       Gwen
      </td>
      <td>
       bird
      </td>
      <td>
      </td>
      <td>
       1997-12-09
      </td>
      <td>
      </td>
     </tr>
     <tr>
      <th scope="row">
       Slim
      </th>
      <td>
       Benny
      </td>
      <td>
       snake
      </td>
      <td>
       m
      </td>
      <td>
       1996-04-29
      </td>
      <td>
      </td>
     </tr>
    </tbody>
   </table>
  </div>
  <p>
   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.
  </p>
  <p>
   You could create a text file
   <code class="filename">
    pet.txt
   </code>
   containing one record per line, with values separated by tabs,
        and given in the order in which the columns were listed in the
   <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    <code class="literal">
     CREATE TABLE
    </code>
   </a>
   statement. For
        missing values (such as unknown sexes or death dates for animals
        that are still living), you can use
   <code class="literal">
    NULL
   </code>
   values. To represent these in your text file, use
   <code class="literal">
    \N
   </code>
   (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):
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-none"><div class="docs-select-all right" id="sa99773372"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-none">Whistler        Gwen    bird    \N      1997-12-09      \N</code></pre>
  </div>
  <p>
   To load the text file
   <code class="filename">
    pet.txt
   </code>
   into the
   <code class="literal">
    pet
   </code>
   table, use this statement:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa89099957"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">LOAD</span> <span class="token keyword">DATA</span> <span class="token keyword">LOCAL</span> <span class="token keyword">INFILE</span> <span class="token string">'/path/pet.txt'</span> <span class="token keyword">INTO</span> <span class="token keyword">TABLE</span> pet<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   If you created the file on Windows with an editor that uses
   <code class="literal">
    \r\n
   </code>
   as a line terminator, you should use
        this statement instead:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa40976185"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">LOAD</span> <span class="token keyword">DATA</span> <span class="token keyword">LOCAL</span> <span class="token keyword">INFILE</span> <span class="token string">'/path/pet.txt'</span> <span class="token keyword">INTO</span> <span class="token keyword">TABLE</span> pet
       <span class="token keyword">LINES</span> <span class="token keyword">TERMINATED</span> <span class="token keyword">BY</span> <span class="token string">'\r\n'</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   (On an Apple machine running macOS, you would likely want to use
   <code class="literal">
    LINES TERMINATED BY '\r'
   </code>
   .)
  </p>
  <p>
   You can specify the column value separator and end of line
        marker explicitly in the
   <a class="link" href="load-data.html" title="15.2.9 LOAD DATA Statement">
    <code class="literal">
     LOAD
        DATA
    </code>
   </a>
   statement if you wish, but the defaults are tab
        and linefeed. These are sufficient for the statement to read the
        file
   <code class="filename">
    pet.txt
   </code>
   properly.
  </p>
  <p>
   If the statement fails, it is likely that your MySQL
        installation does not have local file capability enabled by
        default. See
   <a class="xref" href="load-data-local-security.html" title="8.1.6 Security Considerations for LOAD DATA LOCAL">
    Section 8.1.6, “Security Considerations for LOAD DATA LOCAL”
   </a>
   , for
        information on how to change this.
  </p>
  <p>
   When you want to add new records one at a time, the
   <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
    <code class="literal">
     INSERT
    </code>
   </a>
   statement is useful. In
        its simplest form, you supply values for each column, in the
        order in which the columns were listed in the
   <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    <code class="literal">
     CREATE TABLE
    </code>
   </a>
   statement. Suppose
        that Diane gets a new hamster named
   <span class="quote">
    “
    <span class="quote">
     Puffball.
    </span>
    ”
   </span>
   You
        could add a new record using an
   <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
    <code class="literal">
     INSERT
    </code>
   </a>
   statement like this:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa68604746"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> pet
       <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'Puffball'</span><span class="token punctuation">,</span><span class="token string">'Diane'</span><span class="token punctuation">,</span><span class="token string">'hamster'</span><span class="token punctuation">,</span><span class="token string">'f'</span><span class="token punctuation">,</span><span class="token string">'1999-03-30'</span><span class="token punctuation">,</span><span class="token boolean">NULL</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   String and date values are specified as quoted strings here.
        Also, with
   <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
    <code class="literal">
     INSERT
    </code>
   </a>
   , you can insert
   <code class="literal">
    NULL
   </code>
   directly to represent a missing value.
        You do not use
   <code class="literal">
    \N
   </code>
   like you do with
   <a class="link" href="load-data.html" title="15.2.9 LOAD DATA Statement">
    <code class="literal">
     LOAD DATA
    </code>
   </a>
   .
  </p>
  <p>
   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
   <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
    <code class="literal">
     INSERT
    </code>
   </a>
   statements rather
        than a single
   <a class="link" href="load-data.html" title="15.2.9 LOAD DATA Statement">
    <code class="literal">
     LOAD DATA
    </code>
   </a>
   statement.
  </p>
 </div>
 <br/>
</div>
