CODEWITHSUNDEEP
mysql
Egi Dijus
Egi Dijus

Reputation: 25

Mysql insert multiple rows with random values

Hello i am trying insert 100-200 new rows to user_acc table , but for some reason my current code do nothing . 

DELIMITER //
DECLARE x INT DEFAULT 0;

 WHILE x < 200 DO
INSERT INTO `user_acc`(`playRegion`, `firsttimelogin`) VALUES
(RAND() * (6)+1,1) ;
SET x = x +1;
 END WHILE;
DELIMITER ;

so i am adding playRegion and firsttimelogin for each of row , playRegion is random 1-5 and firstimelogin is walways 1. once i try to execute query nothing happens no error nothing .

Upvotes: 2

Views: 4431

Answers (3)

Ike Walker
Ike Walker

Reputation: 65547

It looks like you are trying to execute an anonymous code block, which MySQL does not support.

You could create a named stored procedure to execute that code.

Rather than implement your own stored procedure, my suggestion is to use the mysqlslap utility, which makes it easy to generate test data.

This command should work for you:

mysqlslap --iterations 200 \
  --create-schema=test \
  --query="INSERT INTO user_acc(playRegion, firsttimelogin) VALUES (RAND() * (6)+1,1) ;"

Upvotes: 0

EoinS
EoinS

Reputation: 5482

Here is an alternative with a stored proc that uses Loop:

DROP PROCEDURE IF EXISTS proc_loop_test;
CREATE PROCEDURE proc_loop_test()
BEGIN
  DECLARE int_val INT DEFAULT 0;
  test_loop : LOOP
      IF (int_val = 10) THEN
      LEAVE test_loop;
    END IF;
   INSERT INTO `user_acc`(`playRegion`, `firsttimelogin`) VALUES
   (RAND() * (6)+1,1) ;
    SET int_val = int_val +1;
  END LOOP; 
END;

call proc_loop_test;

select * from `user_acc`;

I limited to 10 just for testing

Functional Example that you can play around with

Credit for this solution

Upvotes: 5

hassan
hassan

Reputation: 8288

DECLARE Syntax

DECLARE is permitted only inside a BEGIN ... END compound statement and must be at its start, before any other statements.

Upvotes: 0

Related Questions