Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Which DBMS is this? –  a_horse_with_no_name May 18 '12 at 8:05
    
MySQL with InnoDB SE –  john.locke May 18 '12 at 8:07
1  
Can't you use ON DUPLICATE KEY UPDATE ? –  ypercube May 18 '12 at 8:23
1  
Or INSERT IGNORE –  ypercube May 18 '12 at 8:24
1  
See this answer for differences: “INSERT IGNORE” vs “INSERT … ON DUPLICATE KEY UPDATE” –  ypercube May 18 '12 at 8:26

1 Answer 1

up vote 0 down vote accepted

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 ;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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