The data file will be imported many times, data entry personnel will enter information into a text file, which will then be imported amass into a temporary file, the data from the temporary file will be inserted row by row into two different tables. Has to be row by row, as when one row is inserted into the first table, an id is generated to be used for inserting into the second table.
Code sample (not fully tested is below)
CREATE TEMPORARY TABLE attendeeAndSession
(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(127) NOT NULL,
email VARCHAR(127),
phone VARCHAR(20),
affiliationID INT,
statusID INT,
sessionID INT,
notes TEXT,
regStatus ENUM ('unconfirmed', 'confirmed', 'duplicate', 'error', 'cancelled')
);
LOAD DATA INFILE "c:\\\\Temp\\\\attendee4.txt" INTO TABLE attendeeAndSession
LINES STARTING BY 0
IGNORE 1 LINES
(@dummy, name, email, phone,affiliationID, statusID, sessionID, notes, regStatus);
SELECT @attendees = COUNT(*) FROM attendeeAndSession;
SET @rowCount := 0;
SELECT @idCount = MIN(id) FROM attendeeAndSession;
DELIMITER //
WHILE @rowCount < @attendees DO
INSERT INTO attendee (affiliationID, cuStatusID, name, email, phone,notes)
SELECT affiliationID, statusID, name, email, phone, notes
FROM attendeeAndSession WHERE id = @idCount;
INSERT INTO attendeeSession(attendeeID, sessionID, regStatus);
SELECT LAST_INSERT_ID(), sessionID, regStatus from attendeeAndSession;
SET @rowCount:= @rowCount +1;
SET @idCount := @idCount + 1;
END WHILE;
END //
I am hoping to run the stored procedure from a web page so would prefer not to use a bat file, any other work around?