I am using MySQL Workbench. I am trying to build a table with random dummy data that I am generating using a loop. I want to loop through 100 columns which are named 1-100. How can this be accomplished?

Here is what I have so far. I get Error Code: 1054 Unknown column 'count' in 'field list'.

DELIMITER $$
DROP PROCEDURE IF EXISTS insertUsingLoop3$$
CREATE PROCEDURE insertUsingLoop3()
BEGIN
   DECLARE count INT DEFAULT 0;
   DECLARE randValue INT DEFAULT 33;
   
   WHILE count < 101 DO
        SET randValue = FLOOR( RAND() * (127-33)   33);
        INSERT INTO test1(count)
            VALUES(CHAR(randValue));
      SET count = count   1;
   END WHILE;
END$$
DELIMITER ; 

I was hoping to use the variable - 'count' as the column name since I named 100 columns using the numbers 1 - 100.

CodePudding user response：

You can create a query as a string and then execute it. In your case, the query may be as follows:

WHILE count < 101 DO
   SET randValue = FLOOR( RAND() * (127-33)   33);
   SET @q = CONCAT('INSERT INTO test1(', count,') VALUES(CHAR( ',randValue, '))');
   PREPARE stmt FROM @q;
   EXECUTE stmt;
   DEALLOCATE PREPARE stmt;
   SET count = count   1;
END WHILE;

CodePudding user response：

You want to make sure your table test1 has a column for the count and a column for the value.

For example:

When performing your while loop, you want to insert the value of count into the count column of your table and insert the randomValue into the randValue part of the table. When performing an INSERT INTO, the section in () defines the names of columns you wish to add values into--as defined in the VALUES section. With the table mentioned above, you would execute a statement like:

INSERT INTO test1(count, randValue) VALUES (<your count variable>, <your randValue variable>)

count and randValue are the names of your columns, <your count variable> and <your randValue variable> are your values.

Bonus Points

You can omit the () part of test1() if you are adding values for all columns which do not auto increment.