Load CSV into Stored Procedure to Insert into Multiple Tables

csvMySQLstored-procedures

OK, an example is often easier to follow, so here is my procedure:

delimiter //
CREATE PROCEDURE mysql.insertTables (_first_name varchar(30), _last_name varchar(45), _create_time timestamp, _update_time datetime,  _hashid int,
  _id_status bit(2), _id int, _criminal_status bit(1), _dob int, _stateid int, _stateid_status bit(1))
begin
  START TRANSACTION;

  INSERT INTO MYDB.person (`first_name`, `last_name`, `hashID`)
    VALUES (_first_name, _last_name, _hashid);

  INSERT INTO MYDB.id_crimes (`id`, `id_status`, `criminal_status`, `hashID`)
    VALUES (_id, _id_status, _criminal_status, LAST_INSERT_ID());

  INSERT INTO MYDB.dob_state (`stateID`, `stateID_status`, `dob`, `hashID`)
    VALUES (_stateid, _stateid_status, _dob, LAST_INSERT_ID());

  INSERT INTO MYDB.timestamps (`create_time`, `update_time`, `hashID`)
    VALUES (_create_time, _update_time, LAST_INSERT_ID());

  COMMIT;
end//
delimiter ;

Now, what I'd like to know is whether I can load a CSV file to populate the tables directly using that procedure. I've seen workarounds for how to load to a single table, but this is to multiple tables. Any thoughts?

Best Answer

Here is my thought:

Create a table with all the fields that exist in the csv file.
Create a trigger (After insert on the table created in step 1) that calls your procedure that you already have, or let the trigger itself insert new values to the corresponding tables.
Load the CSV file to the table you created in step 1
The other tables will be populated automatically (Nothing to do here)
Drop the table that you created in step 1

Related Solutions

Mysql – Import CSV file into Mysql with Multiple Delimiters/Field Separators

If you're on *nix - check out the tools sed, awk, grep and split and related (or even vi). As mentioned perl could do it, so could python or PHP (or C or Java or ...) This looks more like a task for programming tools rather than a database. That's not to say you couldn't do this using PL/SQL or T-SQL or , but sometimes the most suitable tool is not within the database server (and in this case, certainly not within MySQL).

MySQL – Stored Procedure Performance Using Temporary Tables

To answer a question that arises from the comments, temporary tables in MySQL are owned by and only available to the client connection (session, thread) that created them, so there's not an isolation issue, but there is the potential issue of your connection having left something there with that name before, either with stale data or a similarly-named table with a different structure, either of which would be less than ideal.

The CREATE TEMPORARY TABLE IF NOT EXISTS statement creates the temporary table if there isn't one of the same name, but is a non-blocking no-op if the table is already there, which, as noted, is still bad. The better alternative is probably to drop the temporary table at the top of the proc, if it's there, before creating it, so you always begin with a clean environment. This is similarly a no-op if the temporary table isn't there:

   DROP TEMPORARY TABLE IF EXISTS BATCH_ITEMS_UOW;

Important: if you do not have an index on the STATUS column of the BATCH_ITEMS table, this will be one of the two major reasons this procedure is slower than it needs to be. That's going to be essential for performance, either with this code or the existing code.

It would probably also be best to index the temporary table, to make life as easy as possible for the optimizer. Note that the column declared must be the same as the name or alias of the column in the SELECT, otherwise MySQL assumes the SELECT is supposed to generate additional columns for the temporary table using the column names in the SELECT:

   CREATE TEMPORARY TABLE BATCH_ITEMS_UOW (
     ID INT NOT NULL PRIMARY KEY
   ) ENGINE=MEMORY AS (
    SELECT ID FROM BATCH_ITEMS 
     WHERE STATUS = 'UNPROCESSED' 
       AND BATCH_JOB_ID = batchId 
     LIMIT size
   );

Then instead of an update that use a subquery, this should be a join, since those are generally optimized better in any version of MySQL. This is the other likely reason this procedure may be slower than you expect.

UPDATE BATCH_ITEMS BI
  JOIN BATCH_ITEMS_UOW UOW ON UOW.ID = BI.ID
   SET BI.STATUS = 'PROCESSING';

Then return the result-set...

SELECT * FROM BATCH_ITEMS_UOW;

Optionally, you can drop the temporary table... it will be dropped automatically when your session disconnects, or the next time you run this procedure, since we're now explicitly dropping it at the start if you run this again in the same session.

DROP TEMPORARY TABLE BATCH_ITEMS_UOW;

Best Answer

Related Solutions

Mysql – Import CSV file into Mysql with Multiple Delimiters/Field Separators

MySQL – Stored Procedure Performance Using Temporary Tables

Related Question