Stored procedures workaround for load data infile

Databases
1

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

2

how many times are you planning to load data?

you mentioned importing “a text file”

why don’t you just load it? why do you need a stored procedure?

3

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?

4

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

:slight_smile:

5

while running the section of the code


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?