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.
source to share
There are several problems here.
-
varchar
is not a valid type forconvert
.char
- Don't include parentheses in quotes around names
- You need half a day after the end, and
:
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//
source to share
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
source to share