Auto insert values to table from range

Question

Hello !

Is it possible to auto insert values into a table i.e. from 30 to 200 with MySQL statement ?

Answer 1

I don't know about creating ranges with a SQL statement, but you can very quickly generate a set of data with http://www.generatedata.com/ and run the query to insert it all into your database.

You can have it output the data in SQL format so you just copy & paste into whatever you use to interface with your database. (phpMyAdmin in my case)

Answer 2

I find bulk-insert tasks more manageable when using a programming language to help out.

For example, Python can do what you want in a few lines:

>>> import MySQLdb # import the MySQLdb module
>>> conn = MySQLdb.connect(passwd='mypassword', db='mydb', user='myusername')

>>> mylist = [(i,'abc') for i in range(30,201)] # create list w/ values to insert

>>> curs = conn.cursor()
>>> curs.executemany('''INSERT INTO mytable 
...   (col_with_range, val) VALUES (%s, %s)''', mylist)
171L

And you're done:

>>> curs.execute('SELECT col_with_range, val FROM mytable')
171L
>>> curs.fetchall()
(('30', 'abc'), ('31', 'abc'), ('32', 'abc'), ('33', 'abc'), etc., etc., etc.
>>> curs.close()
>>> conn.close()

Answer 3

Here's a stored procedure that should do it:

CREATE PROCEDURE insert_range()
BEGIN
  DECLARE i INT DEFAULT 30;
  WHILE i <= 200 DO
    INSERT my_table (col_with_range) VALUES (i);
    SET i = i + 1;
  END WHILE;
END