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
books table and our
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 ...