3

Please excuse any syntax errors in my examples; I am new to SQL. For this question, let us suppose I have this hypothetical structure:

authors_list:

  • author_id INT NOT_NULL AUTO_INCREMENT PRIMARY
  • author_name VARCHAR(30) NOT_NULL

books_list:

  • book_id INT NOT_NULL AUTO_INCREMENT PRIMARY
  • book_author_id INT NOT_NULL FOREIGN_KEY(authors_list.author_id)
  • book_name VARCHAR(30) NOT_NULL

Generally when importing books, I would only know the book name and author name. I have finally figured out how to insert into books_list using only this data:

INSERT INTO `books_list`(`book_author_id`, `book_name`) VALUES ((SELECT `author_id` FROM `authors_list` WHERE `author_name` = 'SomeAuthorName'), 'SomeBookName')

However, I have a .csv file which only contains the columns author_name and book_name. I have previously been importing .csv files with phpMyAdmin, but those tables did not have foreign keys. Is there any way to import a .csv of the form described using this "on the fly lookup" functionality?

gautam
  • 302
  • 5
  • 17
  • @Fanda would this be an SQL statement or something in PHP or the like? For now I've been taking the .csv into Excel and doing a VLOOKUP on a local copy of the authors table to get the `id` -- seems like the process you're suggesting would take about the same amount of time? – gautam Jun 25 '14 at 17:41

1 Answers1

3

You can use SQL directly: http://dev.mysql.com/doc/refman/5.1/en/load-data.html

If you need more logic than id generation, you can import data into another table and then write script or procedure to copy data from this table to books_list, using some your customized logic.

If our steps works, use it. There will be probably limit in amount of data. If you reach the limit, use suggested way.

Fanda
  • 3,760
  • 5
  • 37
  • 56
  • I ended up creating a `books_inserter` table with the same structure as my CSV, then wrote a trigger on that which would do the required lookup and insert the data into `books_list`. Probably not the most efficient way, and as of now I have to manually clear the `books_inserter` table after some amount of time, but it gets the job done. Thanks for pointing me in the right direction! (I don't have enough rep to +1 you yet, but the intent is there!) – gautam Jun 26 '14 at 23:42
  • You can (should) use "Mark as answer". It Is the proper way how to use this site. – Fanda Jun 27 '14 at 03:23