Database Reference
In-Depth Information
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
IGNORE 1 LINES
(id, change_type, @niente, @niente,
scientific_name, english_name,
@niente, bird_order, family, @niente,
@niente, @niente, @niente, @niente,
@niente, @niente, @niente, @niente);
Query OK, 32180 rows affected (0.66 sec)
Records: 32180 Deleted: 0 Skipped: 0 Warnings: 0
The list of columns and variables are in the order of the fields in the CSV data text file.
The fields we want to store in the table have the names of the columns with which
MySQL is to associate them. They're in a different order from the table, but MySQL will
handle them the way we want. The contents of the fields we want are stored in the
@ni-
ente
variable, replacing its value each time. This works fine and without any warnings.
Let's select the last two new species from the table to see how the data looks now:
SELECT * FROM rookery.clements_list_import
WHERE change_type='new species'
ORDER BY id DESC LIMIT 2 \G
*************************** 1. row ***************************
id: 30193
scientific_name: Sporophila beltoni
english_name: Tropeiro Seedeater
family: Thraupidae (Tanagers and Allies)
bird_order: Passeriformes
change_type: new species
*************************** 2. row ***************************
id: 26879
scientific_name: Zoothera atrigena
english_name: Bougainville Thrush
family: Turdidae (Thrushes and Allies)
bird_order: Passeriformes
change_type: new species
Your results might be different, depending on which file you downloaded from Cornell's
site. We can see here, though, that the data is in the correct columns. We can now simply
runthe
INSERT INTO...SELECT
statement to copy the new bird species into the
birds_new
and then to the
birds
table — or directly to the
birds
table if we're feel-
ing confident about our abilities to import data. This is much better than our first pass at
loading the data, but we can do better. Let's try loading the data again, but this time let's
get rid of those commonnames in the
family
column.
