mysqlstored-procedures

How to prevent error with counter in procedure


I have a procedure which I use for a while now. I works fine but it shows me also an error after completion. Here's the procedure:

CREATE PROCEDURE `import_U_P`()
BEGIN

DECLARE anzahl INT DEFAULT 0;
DECLARE zaehler INT DEFAULT 0;

SELECT COUNT(*) FROM help_import_U_P INTO anzahl;
SET zaehler = 0;
WHILE zaehler < anzahl DO
  
-- Einfügen der Felder in Personen
INSERT INTO eitw_Personen(PersID, P_Aktiv)
    SELECT PersID, P_Aktiv
    FROM help_import_U_P WHERE P_bekannt NOT LIKE "%Y%"; 
    
SET zaehler = zaehler + 1;
END WHILE;

END;

and this is the error I get:

Query 1 ERROR at Line 1: : Duplicate entry '23785' for key 'eitw_personen.PRIMARY'

where 23785 is die ID of the first record I am inserting with the procedure. I guess it is a problem with the counter, like he counts one too much - therefore I tried to set zaehler = 1 and also on the other side: WHILE zaehler < anzahl-1 DO. but I always get the same error. Now I am running out of ideas. Anybody knows how to prevent the error?

Cheers Ralf


Solution

  • You don't need a WHILE loop to copy a set of rows. If the SELECT matches all the rows you need, it will insert that set of rows in one statement.

    CREATE PROCEDURE `import_U_P`()
    BEGIN
    
    INSERT INTO eitw_Personen(PersID, P_Aktiv)
        SELECT PersID, P_Aktiv
        FROM help_import_U_P WHERE P_bekannt NOT LIKE '%Y%'; 
        
    END