-2

Hi I am trying to populate my years table with year start from 1881 to current year. I have just two field in my years table id and year. How do I do this with SQL query.

something like this

id year    
1 1882
2 1883
3 1884
4 1885
..
id 2016

Thank you

sanu
  • 1,048
  • 3
  • 14
  • 28
  • Please, provide more information about your current query or what you are trying. – Igor O Sep 29 '16 at 03:50
  • sorry i just want to insert the year in years table with id, like (1,1881),(2,1882),(3,1883),(4,1884).......(id,2016) i can do this with php by year increment but i know it can be done in SQL query – sanu Sep 29 '16 at 03:55
  • for now all you need is 1 col (int) primary key – Drew Sep 29 '16 at 03:56
  • But why do you want to do this. I wasn't there for all of it but can confirm that between 1881 and the present there were no missing years. – Strawberry Sep 29 '16 at 06:33

2 Answers2

0

Leverage:

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;

The above from Answer: https://stackoverflow.com/a/9751493

Now for your table

create table years
(   year int primary key
);

insert years(year) select 1881+n
from generator_256
where n<150;
-- 150 rows

select min(year), max(year), count(*) from years;
-- 1881 2030 150

drop view if exists generator_256;
drop view if exists generator_16;

You now have a years table, years 1881 to 2030

optionally,

delete from years where year > year(now());
Community
  • 1
  • 1
Drew
  • 24,851
  • 10
  • 43
  • 78
0

You can create a stored procedure to generate. In this case cal is your calendar table. The idea is to take a start date and then loop over the date and insert until you are done.

DELIMITER //
CREATE PROCEDURE gen(IN start DATE)
BEGIN
  WHILE (SELECT start < NOW()) DO  --- Check our date is before today.
     INSERT INTO cal(`date`) VALUES (start); -- Insert it
     SET start = ADDDATE(start, interval 1 year); -- add a year to it
  END WHILE;
END //
DELIMITER ;

Then you can call it:

CALL gen(DATE('1898-01-01'));
somnium
  • 1,487
  • 9
  • 15