MYSQL inserting records form table A into tables B and C (linked by foreign key) depending on column values in table A

Question

Have been searching high and low for a simple solution to a mysql insert problem. The problem is as follows:

I am putting together an organisational database consisting of departments and desks. A department may or may not have n number of desks.

Both departments and desks have their own table linked by a foreign key in desks to the relevant record in departments (i.e. the pk). I have a temporary table which I use to place all new department data (n records long)...In this table n number of desk records for a department follow the department record directly below. In the TEMP table, if a column department_name has a value,it is a department, if it doesn't it will have a value for the column desk and therefore will be a desk which is related to the above department. As I said there maybe several desk records until you get to the next department record.

Here is a sample of the table records

TEMP{Department,Desk main_telephone, telephone2, telephone3, email, email2, website}

departments(department, telephone1, telephone2, telephone3, email, email2, website)

desks(Desk, foreignkey=Department id, telephone1, telephone2, telephone3, email, email2, website)

Ok, so what I want to do is the following:

From TEMP, Insert the departments into the departments table and their desks into the desks table , generating a foreign key in the desk record to the relevant departments id.

Here is what I have at the moment

INSERT INTO departments(department,main_telephone,telephone2,telephone3,main_fax,website,email,email2)

SELECT Department,Tel1, Tel2, Tel3, Fax, Email, Email2, Web 
  FROM temp WHERE Department != '';

SELECT @last_department := LAST_INSERT_ID();

INSERT INTO department_desks(department_id,department, telephone, extension, telephone2, extension2, telephone3, extension3, fax, email)

SELECT @last_department, Desk,Tel1, Ext1, Tel2, Ext2, Tel3, Ext3,Fax, Email
  FROM temp WHERE Desk != '';

This works except that the last_insert_id returns the first id generated in the above INSERT INTO statment,therefore all desk have the same department_id and I need the id relative to the department...

In pseudo-ish code:

for each record in TEMP table
if Department
   INSERT the record into Departments
   get the id of the newly created Department record and store it somewhere
else if Desk
   INSERT the desk into the desks table with the relevant departments id as the foreignkey

note once again that all departments desks directly follow the department in the TEMP Table

Many Thanks

jun jun · Accepted Answer · 2011-01-08T00:16:31

Table A { id, dept, desk }

Table B { id, foreignkey, dept }

insert into B (id, foreignkey, dept) select null, id, dept from A;

MYSQL inserting records form table A into tables B and C (linked by foreign key) depending on column values in table A

2 Answers