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
3
-
Have a look at this: stackoverflow.com/questions/9764621/… It's using sql server, but you can get an idea as to what you can do.windrunn3r.1990– windrunn3r.19902016-04-22 21:36:26 +00:00Commented Apr 22, 2016 at 21:36
-
you can write a php/python/any language you want script with a loop to do thatfonfonx– fonfonx2016-04-22 21:36:50 +00:00Commented Apr 22, 2016 at 21:36
-
I am using MySql workbench, so where do I write a loop code in java to execute thatuser17422– user174222016-04-22 21:41:23 +00:00Commented Apr 22, 2016 at 21:41
Add a comment
|
2 Answers
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
2 Comments
user17422
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
James Vansteenkiste
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.
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