Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 602
  • Last Modified:

MySQL auto fill table with dates

I have a table named "dbDiary" with fields "fDate" and "fDay".
I want to fill "fDate" field with dates starting July 1st, 2009, in format YYYY-MM-DD and for each value to have the "fDay" field populated with the day number (Monday=1, ..., Sunday=7) - up to 2012, December 31st.

Example of dbDiary table:      

fDate           fTable
--------------------------------
20090713      1
20090714      2
20090715      3
20090716      4
20090717      5
20090718      6
20090719      7
20090720      1
20090721      2
20090722      3
20090723      4
20090724      5
20090725      6
20090726      7
20090727      1
20090728      2
etc..

How can I do this?
0
N M
Asked:
N M
  • 3
  • 2
1 Solution
 
Steve BinkCommented:
You could build a simple stored procedure to make this happen, but the better question is why you feel you need to.  What is the purpose behind populating the table in this manner?
0
 
snoyes_jwCommented:
Insert one record, then insert...select a few times to get the rest of the records:
INSERT INTO dbDiary (fDate) VALUES ('2009-07-01');
 
INSERT INTO dbDiary (fDate) SELECT fDate + INTERVAL (SELECT COUNT(*) FROM dbDiary) DAY FROM dbDiary;
-- Repeat that last insert 10 times, which will populate the table up through 2015
 
DELETE FROM dbDiary WHERE fDate > '2012-12-31';
UPDATE dbDiary SET fDay = DAYOFWEEK(fDate);

Open in new window

0
 
N MConsultantAuthor Commented:
Answering to routinet.

Your comment is valid.
I have a number of tables that need to be loaded each Monday, Tuesday, Wednesday etc. On each day (first column) I want to load files in tables ending in '1' (for example, file blabla.txt into table BlaBla_1, on next day, suppose a Tuesday, file blabla.txt into table BlaBla_2, next day on BlaBla_3 etc).
I need to have a file from each day of the week from another system into MySql but on a weekly basis. Again, using the same example, after a week, I need to overwrite table BlaBla_1 with the new blabla.txt file etc.
 
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
N MConsultantAuthor Commented:
Answering to snoyes_jw

Please allow me to get back to you, I am now checking this.. Thank you for comment..
0
 
snoyes_jwCommented:
Instead of all those separate tables, why not one table, with a field that indicates the day of the week? Then you can delete the old records and insert the new ones, without all the dynamic queries and such that multiple tables imply.

Using 5.1's partitioning would make it even easier.
0
 
N MConsultantAuthor Commented:
Thank you, it worked, and I also made it in a stored procedure.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now