MYSQL Procedure : Loop over distinct values of Text column and create temporary tables

My subgroups table has

subgroup_abbr_str
AA
BB
BB
CC
DD
DD
DD
... and many more

I want to create a temporary table for each of these values. Temporary Table AA, BB, CC and DD. Here is my initial code which creates table var instead of AA.

CREATE PROCEDURE FLAGITERATION()
BEGIN
DECLARE totRows INT DEFAULT 0;
DECLARE startRow INT DEFAULT 0;
DECLARE var TEXT DEFAULT  NULL;
SELECT COUNT(*) FROM subgroups INTO totRows ;
SET startRow=0;
WHILE startRow<totRows DO 
    SELECT DISTINCT subgroup_abbr_str FROM subgroups LIMIT startRow,1 INTO var ;
    CREATE TEMPORARY TABLE var AS SELECT SUM(present)
    FROM trad_new;
  SET startRow = startRow + 1;
END WHILE;
End;
;;

Answer

It costs more time rebuilding sample data with all table as to correct programming errors

What you want to do could be done with dynamic sql.

Also you should use a cursor for the loop

CREATE TABLE subgroups (subgroup_abbr_str VaRCHAR(2));
INSERT INTO subgroups VALUES ('AA'),
('BB'),
('BB'),
('CC'),
('DD'),
('DD'),
('DD')
CREATE tABLE trad_new (present INT)
INSERT INTO trad_new VALUEs (1)
CREATE PROCEDURE FLAGITERATION()
BEGIN
  DECLARE finished INTEGER DEFAULT 0;
  DECLARE _subgroup varchar(100) DEFAULT "";
  -- declare cursor for subgroup_abbr_str
  DEClARE cursubgroup 
      CURSOR FOR 
          SELECT DISTINCT subgroup_abbr_str FROM subgroups;

  -- declare NOT FOUND handler
  DECLARE CONTINUE HANDLER 
        FOR NOT FOUND SET finished = 1;
        
  OPEN cursubgroup;

  getsubgroup: LOOP
      FETCH cursubgroup INTO _subgroup;
      IF finished = 1 THEN 
          LEAVE getsubgroup;
      -- build taböe
      SET @sql = CONCAT("CREATE TEMPORARY TABLE ",_subgroup," AS SELECT SUM(present)
         FROM trad_new;");
         PREPARE stmt FROM @sql;
         EXECUTE stmt;

  END LOOP getsubgroup;
  CLOSE cursubgroup;  
DEALLOCATE PREPARE stmt;  
End
call FLAGITERATION()
SELECT * FROM AA;
| SUM(present) |
| -----------: |
|            1 |

db<>fiddle here

Leave a Reply

Your email address will not be published. Required fields are marked *