How to loop through values and insert them into a database

Question

How can I loop through values and insert them into a database. Specifically what I am doing is this: I have a years table that I want to look something like this:

years
id  name
1   2015*
2   2014
3   2013
etc etc

(2015 is used here because this question was made in 2014, and I want to make the first field the next year of whatever the current year is - eg - if you're reading this question in 2020, the first row should be 2021).

The id field is auto_incremented, and I have tried this after some searching around...

CREATE PROCEDURE `vehicle_years_data()`
BEGIN
    DECLARE i INT DEFAULT YEAR(CURDATE()) + 1;
    WHILE (i >= 1900) DO
        INSERT INTO `vehicle_years` (`name`) VALUE(CONVERT(i, VARCHAR(255));
        SET i = i - 1;
    END WHILE
END$$

DELIMITER ;

CALL `vehicle_years_data()`;

DROP PROCEDURE `vehicle_years_data()`;

But that gives me:

You have an error in your SQL syntax; check the
BEGIN DECLARE i INT DEFAULT YEAR(CUR' at line 10

I know the static way to do this is to simply to

INSERT INTO `vehicle_years` (`name`) VALUES
(2015), (2014), (2013), ...

But that is not only more tedious, but I can't make it dynamically start with the current year plus one that way.

Any help would be much appreciated.

possible duplicate of [How do I get a list of numbers in MySQL?](http://stackoverflow.com/questions/10922064/how-do-i-get-a-list-of-numbers-in-mysql) — Bulat, Sep 19 '14 at 21:19

Laurence · Answer 1 · 2014-09-19T20:42:27.983

There's a few problems here

varchar isn't a valid type for convert. char is
Don't include the parenthesis in the quotes around the names
You need a semi colon after the end while

code example:

create procedure `vehicle_years_data`()
begin
    declare i int default year(curdate()) + 1;
    while (i >= 1900) do
        insert into `vehicle_years` (`name`) value (convert(i, char(255)));
        set i = i - 1;
    end while;
end//

Example SQLFiddle

score 1 · Answer 2 · answered Sep 19 '14 at 21:14

Here is an alternative to procedure based approach:

CREATE OR REPLACE VIEW generator_16
AS SELECT 0 n UNION ALL SELECT 1  UNION ALL SELECT 2  UNION ALL 
   SELECT 3   UNION ALL SELECT 4  UNION ALL SELECT 5  UNION ALL
   SELECT 6   UNION ALL SELECT 7  UNION ALL SELECT 8  UNION ALL
   SELECT 9   UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL
   SELECT 12  UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL 
   SELECT 15;

CREATE OR REPLACE VIEW generator_256
AS SELECT ( ( hi.n << 4 ) | lo.n ) AS n
     FROM generator_16 lo, generator_16 hi;

INSERT INTO `vehicle_years` (`name`)
select  year(curdate()) - n from generator_256

http://use-the-index-luke.com/blog/2011-07-30/mysql-row-generator#mysql_generator_code — Bulat, Sep 19 '14 at 21:14

How to loop through values and insert them into a database

2 Answers2