Loading Data into a Table

Posted on February 4, 2012. Filed under: mysql |

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  
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 (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):
Whistler        Gwen    bird    \N      1997-12-09      \N
To load the text file pet.txt into the pet table, use this statement:
mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet;
If you created the file on Windows with an editor that uses \r\n as a line terminator, you should use this statement instead:
mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet
-> LINES TERMINATED BY '\r\n';
(On an Apple machine running OS X, you would likely want to use LINES TERMINATED BY '\r'.)
You can specify the column value separator and end of line marker explicitly in the LOAD DATAstatement 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 Section 5.3.5, “Security Issues with 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 TABLEstatement. 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);
String and date values are specified as quoted strings here. Also, with INSERT, you can insert NULLdirectly 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.

Make a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

One Response to “Loading Data into a Table”

RSS Feed for I Know This Now Comments RSS Feed

This is a very important commands for a novice person.

To insert xls file through the above mentioned method


Where's The Comment Form?

Liked it here?
Why not try sites on the blogroll...

%d bloggers like this: