MySQL in a Nutshell

Importing Data in Bulk

While the INSERT and REPLACE statements are useful, they can be time-consuming when you’re entering a large number of rows, because they’re somewhat manual methods of entering data. Often, when setting up a new database, you will need to migrate data from an old database to MySQL. In the case of our bookstore, let’s suppose that a vendor has sent us a disk with a list of all of their books in a simple text file. Each record for each book is on a separate line and each field of each record is separated by a vertical bar. Here’s how the fictitious vendor’s data text file looks:

ISBN|TITLE|AUTHOR LAST|AUTHOR FIRST|COPYRIGHT DATE|
067973452X|Notes from Underground|Dostoevsky|Fyodor|August 1994|
...

Obviously, an actual vendor file would contain more fields and records than are shown here, but this is enough for our example. The first line contains descriptions of the fields in the records that follow. We don’t need to extract the first line; it’s just instructions for us. So, we’ll tell MySQL to ignore it when we enter our SQL statement. As for the data, we must consider a few problems: the fields are not in the order that they are found in our tables. We’ll have to tell MySQL the order in which the data will be coming so that it can make adjustments. The other problem is that this text table contains data for our books table and our authors table. This is going to be a bit tricky, but we can deal with it. What we’ll do is extract the author information only in ...