0

I have a problem where I need to copy a table from MYSQL using this:

CREATE TABLE newtable LIKE oldtable; 
INSERT newtable SELECT * FROM oldtable;

I need to drop that table weekly and create them again because I dont want to lose the original data. Can anyone help me to find a solution where it can be done automatically. I really have no idea about that

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
jbabe
  • 11
  • 4

2 Answers2

0

What if you have a java program, where you're having the MySQL query and let the query be executed automatically through a cron job periodically.

Where you can maybe have two separate threads running, or you could do it within the same thread it self. ie, executing the INSERT into the newtable and then maybe you can simply drop the old ones.

OR you could go with the MySQL Event Scheduler. This SO pretty much explains the effective way of going ahead with this as per your requirement.

Community
  • 1
  • 1
Kulasangar
  • 9,046
  • 5
  • 51
  • 82
0

You can use MySQL EVENT Scheduler for this very purpose like below. You can as well consider using CREATE TABLE AS(CTAS) construct instead

delimiter |
CREATE EVENT myevent
    ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 WEEK
    DO
BEGIN
      DROP TABLE IF EXISTS newtable;
      CREATE TABLE newtable AS  
         SELECT * FROM oldtable;
END |
delimiter ;
Rahul
  • 76,197
  • 13
  • 71
  • 125
  • It's a good method, thanks! Will try it. But can I know where to create this event. is it inside the sql in localhost? – jbabe Dec 18 '16 at 14:26
  • @NorNazirah, it's a SQL statement and thus create it likewise you create any other DB objects like stored procedure or functions. – Rahul Dec 18 '16 at 14:29
  • i tried to run it in sql but it say that SCHEDULE is unrecognized statement. why? – jbabe Dec 18 '16 at 15:11
  • @NorNazirah, See edit in answer for a full syntax of creating event in MySQL. Refer the linked MySQL documentation page for more information. Also, which MySQL version you are running on? – Rahul Dec 18 '16 at 18:43