loop through column names and insert values

Question

I need help in looping through columns and inserting values to columns.

My Obj:- Insert new value to selective columns in a table using stored-procedure i am using case statement to make selective choice and loop through to update list of columns

My code:

  DELIMITER $$
  USE `test`$$
  DROP PROCEDURE IF EXISTS `loopcolnm1`$$
  CREATE DEFINER=`root`@`localhost` PROCEDURE `loopcolnm1`()

  BEGIN
  DECLARE sColName  VARCHAR(255);
  DECLARE sEgStr  VARCHAR(255);
  DECLARE X  INT;
  SET segstr = ' ';
  SET X = 0;
  Loop_label : LOOP 
  IF  X > 9 THEN LEAVE  loop_label;
  END  IF;
  SET X = X+1;
  CASE 
        WHEN X= 1 THEN  
        SET sColName = 'age' ;
        WHEN X= 2 THEN  SET sColName  = 'Gender' ;
        WHEN X= 3 THEN  SET sColName  = 'incm_rng' ;
        WHEN X= 4 THEN  SET sColName  = 'Prc_rng' ;
        WHEN X= 5 THEN  SET sColName  = 'prop_purp';
        WHEN X= 6 THEN  SET sColName  = 'time_rng'  ;
        WHEN X= 7 THEN  SET sColName  = 'time_rsn' ;
        WHEN X= 8 THEN  SET sColName = 'crrnt_dmcl' ;
        WHEN X= 9 THEN  SET sColName  = 'Prop_Rsn' ;
        ELSE  SET sColName  = '\'All\' as Sub_Crit';

  END CASE ;
  SET sEgStr = CONCAT(sEgstr,' ',X, ' ',',' ,sColName, ' '); -- test variable

  INSERT INTO lptable (sColName) 
  VALUE ( CONCAT('loop + ',X));

  END LOOP; 
  SELECT segstr AS cnct;
      END$$

  DELIMITER ;

alternatively i tried with a similar solution but, on both instance i get error

edit(1) - Additional Information

when i compile the stored procedure it does not throw up any error message however when i call the stored procedure

CALL loopcolnm1();

Error Code: 1054 Unknown column 'sColName' in 'field list'

i am new to mysql, i am not sure if this is something that i can do or i am erring in syntax, kindly help

I don't see `col_name` anywhere in your code, I don't see how that function could be getting that error. — Barmar, Jan 01 '14 at 05:30
When you want to execute dynamic SQL, you have to use `PREPARE` — Barmar, Jan 01 '14 at 05:34
You can only insert whole rows into a table. Also, the normal INSERT statement uses the keyword VALUES, not VALUE. And the column-name is specified before the VALUES clause: `INSERT INTO TableName(ColumnName1, ColumnName2, ...) VALUES(Value1, Value2, ...)`. — Jonathan Leffler, Jan 01 '14 at 05:46
Thank you Barmar, your guidance helped, i am now able to run the code without any issues, — Mysql_NUb_Sri, Jan 01 '14 at 06:52

score 0 · Accepted Answer · answered Jan 01 '14 at 07:32

Your insert statement is wrong. You can't use a variable sColName directly as a column name. You should generate the INSERT query string by concatenating parts of strings.

This is wrong:

INSERT INTO lptable (sColName) VALUE ( CONCAT('loop + ',X));

And You have to use VALUES but not VALUE.

And you also need prepared statement to execute dynamically composed SQL statements.

You would need following changes in your procedure.

BEGIN  
   ...

    DECLARE table_fields_str     VARCHAR( 255 );
    DECLARE values_str           VARCHAR( 255 );
    DECLARE _temp_sql_insert_str VARCHAR( 255 );

   SET table_fields_str = CONCAT( 'INSERT INTO lptable (', sColName, ') ' );
   SET values_str       =  CONCAT( ' VALUES( CONCAT( ''loop '', ', X, ' ) )' );

   SELECT CONCAT( table_fields, values_str ) INTO @_temp_sql_insert_str;

   PREPARE stmt FROM @_temp_sql_insert_str;
   EXECUTE stmt;
END;

Ravinder, this is brilliant stuff... thank you for educating me You just made my new year day merrier :) — Mysql_NUb_Sri, Jan 01 '14 at 09:33

loop through column names and insert values

My code:

1 Answers1