INSERT into three tables from one table

Question

I currently have a bad designed Access database and I'm building a new database using MySQL. In this Access database I have one table, while in MySQL i have three tables to manage the same data thing.

The ERR model is simple. It's an n:m relationship, between the tables "Company" and "Category". Since its an n:m I've got another table called "CompanyCategory".

What I do is to read data from access and make INSERTs into the new DB.

I'm doing a small C# program to do this.

So, I get the Company data from Access and I Insert the data into the new Company table. I get the category name and I Insert it into the Category table. To avoid duplicates into this table, I have a UNIQUE INDEX on the category name. The problem is when I Insert the IDs in the CompanyCategory table, since the INSERT into the Category table usually returns -1 (LastInsertedId method). Who can help me? Thank you.

Answer 1

Hi! Ok, as I understand it, your data looks something like this:

[olddata]
companyid  title  categorytitle
1          Acme   Teapots
2          Cmea   Furniture
3          Meac   Cars
4          Eacm   Furniture

And you like to export that and only add the category 'Furniture' to your new category table once. Here are the new tables:

company (companyid,title)
category (categoryid,title)
companycategory (categoryid,companyid)

And this should transport data from the 'oldtable' into theese new tables:

INSERT INTO company (companyid,title) SELECT companyid,title FROM olddata

INSERT INTO category (title) SELECT DISTINCT categorytitle FROM olddata

INSERT INTO companycategory (companyid,categoryid) SELECT a.companyid,(SELECT b.categoryid FROM category b WHERE b.title=a.categorytitle) FROM olddata a