SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Zealot
    Join Date
    Aug 2006
    Posts
    108
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Stored procedures workaround for load data infile

    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. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Aug 2006
    Posts
    108
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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)
    Code:
    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. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot
    Join Date
    Aug 2006
    Posts
    108
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    while running the section of the code
    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?


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •