inserting values into a column using loop in mysql

Question

I have a single column named Roll No in a table named Student I want to insert values from 100 to 450 in that column and I want to do it without manually inserting all the values from 100 to 450. Is there any way I could do it using a loop ? Thanks

Have a look at this: http://stackoverflow.com/questions/9764621/how-to-insert-multiple-rows-a-loop-needed It's using sql server, but you can get an idea as to what you can do. — windrunn3r.1990, Apr 22 '16 at 21:36
you can write a php/python/any language you want script with a loop to do that — fonfonx, Apr 22 '16 at 21:36
I am using MySql workbench, so where do I write a loop code in java to execute that — user17422, Apr 22 '16 at 21:41

score 3 · Accepted Answer · edited May 23 '17 at 10:28

3

Assuming you don't wish to use any other languages, you are going to have to create a MYSQL procedure to accomplish this.

delimiter //

CREATE PROCEDURE insertproc()
BEGIN
    DECLARE i int DEFAULT 100;
    WHILE i <= 450 DO
        INSERT INTO students (rollNo) VALUES (i);
        SET i = i + 1;
    END WHILE;
END//

delimiter ;

Relevant SQLFiddle: http://sqlfiddle.com/#!2/a1320/2

See this similar question which I used as my source: MySQL Insert with While Loop

edited May 23 '17 at 10:28

Community

1
1

answered Apr 22 '16 at 21:52

James Vansteenkiste

148
8

18:29:33 CREATE PROCEDURE insertproc() BEGIN DECLARE i int DEFAULT 100 Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 3 0.016 sec I am getting this error – user17422 Apr 22 '16 at 22:30
Weird, I seem to get that error too, but only with sqlfiddle. When I enter the above through MYSQL-CommandLine it works fine. I think there is an issue with defining a delimiter with their service. – James Vansteenkiste Apr 22 '16 at 23:10

score -2 · Answer 2 · answered Apr 22 '16 at 21:58

Here is the script u want. i did it in php

<?php
$servername = "";
$username = "";
$password = "";
$dbname = "";

// Create connection
$con = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($con->connect_error) {
    die("Connection failed: " . $con->connect_error);
} 

echo "Connected successfully";

for ($i = 100; $i <= 450; $i++) {
 $sql="insert into student(rollno) values('$i')";
    $result=mysqli_query($con,$sql);
}
mysqli_close($con);
?>

don't forget to enter username,password etc.. then save this in a .php file and run it

inserting values into a column using loop in mysql

2 Answers2