2

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?

2
  • Why "hashID" not "person_id", etc? Let's see SHOW CREATE TABLE
    – Rick James
    May 1, 2016 at 20:40
  • Batch normalization can be done -- see my blog.
    – Rick James
    May 1, 2016 at 20:41

1 Answer 1

1

Here is my thought:

  1. Create a table with all the fields that exist in the csv file.
  2. 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.
  3. Load the CSV file to the table you created in step 1
  4. The other tables will be populated automatically (Nothing to do here)
  5. Drop the table that you created in step 1
2
  • That is a good workaround, so I marked it as correct. I'd really like to know if there is a way to do it more directly. :)
    – Lori
    Apr 27, 2016 at 0:32
  • @Lori Hi. Have you found a more direct way by now? Thanks and greetings.
    – Metafaniel
    Sep 25, 2020 at 5:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.