Using LOAD for easier MySQL data entry

Typing a long sequence of INSERT statements to enter new data is a pain in the neck. This lab will guide you through one alternative.

1. In a Linux terminal window, type the command

   pico authors.txt

   pico is a small, terminal-window-based text editor. You can just start typing to enter text, or you can use the commands at the bottom of the terminal window (Ctrl-O to save, Ctrl-X to Exit, etc.).

2. Enter the following lines of text, save (Ctrl-O) and exit (Ctrl-X). Note that where I show [tab], you should type the tab key.

   3[tab]Jonathan[tab]Swift[tab]1745[tab]England
   4[tab]Harper[tab]Lee[tab]10000[tab]US

3. Execute the command

   cp authors.txt /tmp/yourusername.txt

4. Execute

   Enter mysql (/usr/mysql/bin/mysql -u yourusername -p yourusername), and execute the SQL command

   LOAD DATA INFILE '/tmp/yourusername.txt' INTO TABLE authors;

5. Exit mysql (quit). When you get the prism> prompt back, execute the command

   rm /tmp/yourusername.txt

   The cp (copy) and rm (remove) commands move a copy of your authors.txt file to a place where the LOAD command can find it, and then removes the copy when you're done. The LOAD command moves the data into your authors table.

6. Go into MySQL and execute the SQL command "SELECT * FROM authors;" to see whether your new authors are there.