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? — Bulat
– Bulat, Commented Sep 19, 2014 at 21:19

Laurence · Accepted Answer · 2014-09-19 20:42:27Z

1

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

edited Sep 19, 2014 at 20:42

answered Sep 19, 2014 at 20:36

Laurence

11k1 gold badge28 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bulat · Accepted Answer · 2014-09-19 21:14:21Z

1

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

answered Sep 19, 2014 at 21:14

Bulat

6,9992 gold badges32 silver badges54 bronze badges

1 Comment

Bulat Over a year ago

use-the-index-luke.com/blog/2011-07-30/…

Collectives™ on Stack Overflow

How to loop through values and insert them into a database

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related