We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

MySQL auto fill table with dates

N M
N M asked
on
Medium Priority
621 Views
Last Modified: 2012-05-07
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?
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2004

Commented:
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?
Top Expert 2005
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
N MConsultant

Author

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.
 
N MConsultant

Author

Commented:
Answering to snoyes_jw

Please allow me to get back to you, I am now checking this.. Thank you for comment..
Top Expert 2005

Commented:
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.
N MConsultant

Author

Commented:
Thank you, it worked, and I also made it in a stored procedure.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.