MySQL INSERT using only SQL

Is it possible using just SQL and MySQL to get the "OUTPUT" below?

SAMPLE DATA: To better elaborate with an example, lets assume I am trying to load a file containing employee name, the offices they have occupied in the past and their Job title history separated by a tab.

File:

EmployeeName<tab>OfficeHistory<tab>JobLevelHistory
John Smith<tab>501<tab>Engineer
John Smith<tab>601<tab>Senior Engineer
John Smith<tab>701<tab>Manager
Alex Button<tab>601<tab>Senior Assistant
Alex Button<tab>454<tab>Manager

NOTE: The single table database is completely normalized (as much as a single table may be) -- and for example, in the case of "John Smith" there is only one John Smith; meaning there are no duplicates that would lead to conflicts in referential integrity.

The MyOffice database schema has the following tables:

Employee (nId, name)
Office (nId, number)
JobTitle (nId, titleName)
Employee2Office (nEmpID, nOfficeId)
Employee2JobTitle (nEmpId, nJobTitleID)

OUTPUT: So in this case. the tables should look like:

Employee
1 John Smith
2 Alex Button

Office
1 501
2 601
3 701
4 454

JobTitle
1 Engineer
2 Senior Engineer
3 Manager
4 Senior Assistant

Employee2Office
1 1
1 2
1 3
2 2
2 4

Employee2JobTitle
1 1
1 2
1 3
2 4
2 3

Here's the MySQL DDL to create the database and tables:

create database MyOffice2;

use MyOffice2;

CREATE TABLE Employee (
      id MEDIUMINT NOT NULL AUTO_INCREMENT,
      name CHAR(50) NOT NULL,
      PRIMARY KEY (id)
    ) ENGINE=InnoDB;

CREATE TABLE Office (
  id MEDIUMINT NOT NULL AUTO_INCREMENT,
  office_number INT NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB;

CREATE TABLE JobTitle (
  id MEDIUMINT NOT NULL AUTO_INCREMENT,
  title CHAR(30) NOT NULL,
  PRIMARY KEY (id)
) 开发者_如何学编程ENGINE=InnoDB;

CREATE TABLE Employee2JobTitle (
  employee_id MEDIUMINT NOT NULL,
  job_title_id MEDIUMINT NOT NULL,
  FOREIGN KEY (employee_id) REFERENCES Employee(id),
  FOREIGN KEY (job_title_id) REFERENCES JobTitle(id),
  PRIMARY KEY (employee_id, job_title_id)
) ENGINE=InnoDB;

CREATE TABLE Employee2Office (
  employee_id MEDIUMINT NOT NULL,
  office_id MEDIUMINT NOT NULL,
  FOREIGN KEY (employee_id) REFERENCES Employee(id),
  FOREIGN KEY (office_id) REFERENCES Office(id),
  PRIMARY KEY (employee_id, office_id)
) ENGINE=InnoDB;

---

You can use a pass through table, and a trigger for this. Periodically, or from your calling app, delete from this table whenever you're done with it.

create table TmpEmp (
EmployeeName char(50) not null,
OfficeHistory int null,
JobLevelHistory char(30) null);

Create a trigger on this table

delimiter |
CREATE TRIGGER tg_TmpEmp BEFORE INSERT ON TmpEmp
FOR EACH ROW
BEGIN
IF not exists (select * from Employee where Name = NEW.EmployeeName) THEN
    INSERT INTO Employee(name)
        select NEW.EmployeeName;
END IF;
IF not exists (select * from Office where office_number = NEW.OfficeHistory) THEN
    INSERT INTO Office(office_number)
        select NEW.OfficeHistory;
END IF;
IF not exists (select * from JobTitle where title = NEW.JobLevelHistory) THEN
    INSERT INTO JobTitle(title)
        select NEW.JobLevelHistory;
END IF;
INSERT INTO Employee2JobTitle(employee_id,job_title_id)
    select E.id, T.id
    from Employee E
    inner join JobTitle T on T.title = NEW.JobLevelHistory
    where E.Name = NEW.EmployeeName
        AND not exists (select *
            from Employee2JobTitle J
            where J.employee_id = E.id and J.job_title_id = T.id);
INSERT INTO Employee2Office(employee_id,office_id)
    select E.id, O.id
    from Employee E
    inner join Office O on O.office_number = NEW.OfficeHistory
    where E.Name = NEW.EmployeeName
        AND not exists (select *
            from Employee2Office J
            where J.employee_id = E.id and J.office_id = O.id);
END; |
delimiter ;

Note: The benefit of this trigger and table is that it works whether you are using LOAD-FILE or just plain inserts. The trigger gets fired and adds data where it needs to.

Test it

insert tmpEmp(EmployeeName,OfficeHistory,JobLevelHistory)
select 'John Smith',501,'Engineer' union all
select 'John Smith',601,'Senior Engineer' union all
select 'John Smith',701,'Manager' union all
select 'Alex Button',601,'Senior Assistant' union all
select 'Alex Button',454,'Manager';

truncate table tmpEmp;

---

Maybe you could get it working by using MySQL LOAD DATA INFILE syntax.

Accoring to the specification you can use it like this:

LOAD DATA INFILE 'data.txt' INTO TABLE db2.my_table;

and setting options like this:

FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'
LINES TERMINATED BY '\n' STARTING BY ''

EDIT: Added one proposal:

1) Load the file into a temp file, let's call it table temp (left out in this example)
2) Insert basic data into right tables
  INSERT INTO Employee (name)   
  Select distinct name from temp;

  INSERT INTO Office (office_number)
  Select DISTINCT office from temp;

  INSERT INTO JobTitle (title)
  Select DISTINCT job_level from temp;

3) Create mapping tables by using joins, like:

  INSERT INTO Employee2Office (employee_id, office_id)
  select Employee.id, office.id from temp
  INNER JOIN Employee ON temp.name = Employee.name
  INNER JOIN Office ON temp.office = Office.office_number

  Follow the same approach for the other mapping table. 

---

As for the temp table suggestion, I just don't understand how a temp table would differ from a "real" table; meaning I know what a temp table is, just don't get how it'd make a difference in the load process.

The temp table would allow you to load the data into a single flat table. For example you could implement the following process in SQL:

1. Turn off foreign key checks
2. Create a temp table to hold the data in the flat file as is
3. Load the flat file data into the temp table
4. use INSERT INTO ... SELECT FROM to load the data into your main tables (Employee, JobTitle, Office - so 3 queries)
5. Use a query to select the two main table's auto increment columns based on the correlation of values in the main table and the temp table, and insert those into your join table (you'll do this twice, once for each join table)
6. Turn foreign key checks back on

This is what i meant by it has to be scripted. There is no way that MySQL can magically map the relationships from flat data. You need to do that yourself. You could write it in SQL using the steps above but it just seems like it would be simpler to use a scripting language you're familiar with instead which avoids all kinds of possible permissions/access issues with using LOAD DATA.