Is there a way to skip errors in procedures and resuming procedure

Question

When there is a unique key in a table (not PRIMARY KEY) and procedure is running, on duplicate key error the whole process will be halted. I want to resume on error and call the procedure again.

The procedure:

DELIMITER $$

CREATE PROCEDURE `injatest`.`LoadData` ()
BEGIN

    DECLARE x INT;

    SET x = 1;
    WHILE x <= 14400 DO
        INSERT INTO junc_question_course_iq(q_id,iq_id,ct_id) VALUES
        (CEIL(RAND()*1000), CEIL(RAND()*48), CEIL(RAND()*10));
        SET x = x + 1;
    END WHILE;

END $$


DELIMITER ;  

I want to insert about 14000 records in a junction table, but the problem arise when there is a duplicate key for unique(iq_id,q_id)? what do do?

Answer 1

You can use DECLARE CONTINUE HANDLER FOR SQLSTATE to ignore the key violation by declaring an empty block as an exception handler, as follows:

DELIMITER $$

CREATE PROCEDURE `injatest`.`LoadData` ()
BEGIN

    DECLARE x INT;
    DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' BEGIN END;
    SET x = 1;
    WHILE x <= 14400 DO
        INSERT INTO junc_question_course_iq(q_id,iq_id,ct_id) VALUES
        (CEIL(RAND()*1000), CEIL(RAND()*48), CEIL(RAND()*10));
        SET x = x + 1;
    END WHILE;

END $$

DELIMITER ;