O'Reilly logo

MySQL in a Nutshell, 2nd Edition by Russell J.T. Dyer

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Importing Data in Bulk

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 what the fictitious vendor’s data text file looks like:

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. First, 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 both 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 one SQL statement, then we’ll run a separate SQL statement to import the book information. Before starting, we’ve copied the vendor’s file called books.txt to a temporary ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required