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
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