sql to populate newly created link table

I would be really grateful if somebody could help me out with this..

I actually have 2 tables in my database: books and authorlist.

The book table contains a field 'book_aut' which contains the foreign key of the authorlist table.

The authorlist table has only 2 fields, the primary key and the 'authors' column which contains a list of names.

I have to modify the table structure so that books table is linked to an authors table via a link table called 'lnk_book_author'

So my first task is to create a new table called 'authors' which contains 3 fields - primary id, name, surname, which i already开发者_C百科 did.

Next, i created the link table called 'lnk_book_author' and this one contains 3 fields, the primary id, book_fk, author_fk. The book_fk and author_fk refer to the id of the book and author respectively.

My problem is that i have more than 6000 entries in the books table and i would like to know how to populate the link table with the book id and the author id.

Is there a way of doing that using sql instead of manually populating the lnk_book_author table.

Hope i was clear enough..

Thanks a lot for any suggestion provided.

I'm infering the IDs already in your new [authors] table mean nothing with regards to the old tables. If that's the case you need to relate the records by the Name. And there I need to assume that the names are entered Identically. If they're not, it may not be possible to do. We'd need to know a lot more specifics to be sure...

INSERT INTO
  lnk_book_author
SELECT
  Books.PrimaryKeyFieldName,
  Authors.PrimaryKeyFieldName
FROM
  Books
INNER JOIN
  AuthorList
    ON Books.BookAut = AuthorList.PrimaryKeyFieldName
INNER JOIN
  Authors
    ON CONCAT(',', AuthorList.Authors, ',') LIKE CONCAT('%,', Authors.Name, ',%')

Something like that ?

INSERT INTO lnk_book_author(book_fk,author_fk)
SELECT b.book_id,a.author_id
FROM books b INNER JOIN authorlist a
ON b.book_aut=a.author_id

Try this it should work:

INSERT INTO lnk_book_author (book_fk, author_fk)
VALUES ((SELECT Id FROM books), (SELECT Id FROM authors))

And by the way there's no point having an Id column in the lnk_book_authors table, you may as well just make the foreign keys a composite primary key.

UPDATE

Sorry I realise that would only work with one record, try the following SQL:

INSERT INTO lnk_book_author (book_fk, author_fk)
SELECT books.Id, authors.Id
FROM books, authors