I have written a basic stored procedure, which looks up the contact from one table, links with a membership, and inserts the membership plus an original field (from one table) into the new one.
Unfortunately the entity_id insert is always the same. 13226 for all of the records. I've gone through the logic and query step by step. It would seem somewhere the entity_id value isn't being updated, so the same value gets inserted.
DROP PROCEDURE IF EXISTS data_membership_insert; TRUNCATE test_table; DELIMITER ;; CREATE PROCEDURE data_membership_insert() BEGIN -- Declare variables -- Must be first, else will fail DECLARE DONE INTEGER DEFAULT 0; DECLARE CONTACT_ID INT DEFAULT 0; DECLARE MEMBERSHIP_ID INT DEFAULT 0; DECLARE PRIMARY_KEY INT DEFAULT 0; DECLARE NOMINATOR_CONTACT_ID INT DEFAULT 0; DECLARE COUNT INT DEFAULT 0; DECLARE cursor_i CURSOR FOR ( SELECT id FROM civicrm_value_membership_3 WHERE nominated_by_4 IS NOT NULL AND entity_id IS NOT NULL GROUP BY entity_id ORDER BY id ); DECLARE CONTINUE HANDLER FOR NOT FOUND SET DONE = 1; SET CONTACT_ID = null; SET NOMINATOR_CONTACT_ID = null; SET MEMBERSHIP_ID = null; SET PRIMARY_KEY = null; SET COUNT = null; OPEN cursor_i; read_loop: LOOP FETCH cursor_i INTO PRIMARY_KEY; SET CONTACT_ID = null; SET NOMINATOR_CONTACT_ID = null; SET MEMBERSHIP_ID = null; SET COUNT = null; IF DONE = 1 THEN LEAVE read_loop; END IF; SELECT entity_id FROM civicrm_value_membership_3 WHERE id = PRIMARY_KEY INTO CONTACT_ID; SELECT id FROM civicrm_membership WHERE contact_id = CONTACT_ID LIMIT 1 INTO MEMBERSHIP_ID; SELECT nominated_by_4 FROM civicrm_value_membership_3 WHERE id = PRIMARY_KEY LIMIT 1 INTO NOMINATOR_CONTACT_ID; INSERT INTO test_table ( entity_id, nominated_by_48 ) VALUES( MEMBERSHIP_ID, NOMINATOR_CONTACT_ID ); END LOOP read_loop; CLOSE cursor_i; END; ;; DELIMITER ; CALL data_membership_insert(); DROP PROCEDURE IF EXISTS data_membership_insert;