The command LOAD DATA INFILE cannot be used within a stored procedure, I need to import a text file into a temporary table and from that select portions of it to insert in different tables. I wanted to use LOAD DATA INFILE as its two features of LINES STARTING BY and IGNORE x LINES, would be most useful, but as I cannot use it directly into a stored procedure, is there a workaround, such as putting it into a function and calling that function from the stored procedure, or is there another way around it?
Any help appreciated.
CathyM
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?
well, i can’t help on command inside a stored proc, sorry
as for the auto_increment/LAST_INSERT_ID row-by-row problem, i do understand what you’re trying to do, but there are other ways
there are ~always~ other ways around looping in SQL
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 get an error 1064(42000) about invalid syntax near the start of the while loop, yet if I run the individual statements they work out fine. I can’t seem to see the error at the start of the while loop.
Any ideas?
