How to move values ​​and insert them into the database

How can I iterate over the values ​​and insert them into the database. Specifically I do this: I have a table of years and I want to look something like this:

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

      

(2015 is being used here because this question was made in 2014 and I want to make the first field next year of what will be in the current year - for example, if you are reading this question in 2020, the first line should be 2021).

The id field is auto_incremented and I tried this after doing 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 this 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 simply

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

      

But not only is this more tedious, but I can't get it to dynamically start from the current year plus one of them.

Any help would be much appreciated.

+3


source to share


2 answers


There are several problems here.

  • varchar

    is not a valid type for convert

    .char

  • Don't include parentheses in quotes around names
  • You need half a day after the end, and
Sample code

:



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//

      

SQLFiddle example

+1


source


Here's an alternative to the procedural 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

      

+1


source







All Articles