Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am writing a simple procedure to backup a table-

CREATE PROCEDURE daily_backup()
BEGIN
   DECLARE given_date VARCHAR(25);
   SET given_date = now(); 

   CREATE TABLE given_date LIKE db1.table1;
   INSERT INTO given_date SELECT * FROM db1.table1;


END

But it creates a table with name given_date . I want to create a table with date as name. How to do the same ?

share|improve this question
2  
Cross-posted: stackoverflow.com/q/17106254/330315 –  a_horse_with_no_name Jun 14 '13 at 11:01
    
Please don't cross-post. Thanks. –  Jon Seigel Jun 14 '13 at 17:05
add comment

2 Answers

up vote 3 down vote accepted

Try This one

DROP PROCEDURE IF EXISTS daily_backup;

DELIMITER //

CREATE PROCEDURE daily_backup()
BEGIN
    DECLARE given_datetime VARCHAR(25);
    DECLARE backup_table VARCHAR(64);

    SET given_datetime = DATE_FORMAT(NOW(),'%Y%m%d_%H%i%s');

    /**We need to add backticks to forcibly create table name as current date and time **/
    SET backup_table = CONCAT('db.table1_',given_datetime);
    SET @modified_date = CONCAT('`',backup_table,'`');
    SET @create_sql := CONCAT('CREATE TABLE ', @modified_date, ' LIKE db1.table1;');
    prepare stmt from @create_sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;


    SET @insert_sql := CONCAT('INSERT INTO ', @modified_date, ' SELECT * FROM db1.table1;');
    prepare stmt1 from @insert_sql;
    EXECUTE stmt1;
    DEALLOCATE PREPARE stmt1;


END //
DELIMITER ;
CALL daily_backup();

UPDATE

We cant create table directly as CREATE TABLE 2013-06-14 16:58:37 LIKE db1.table;

But we can create it forcibly by adding backtics in table name as

CREATE TABLE 2013-06-14 16:58:37 LIKE db1.table;

UPDATE by RolandoMySQLDBA

I changed the code to have datetime appended to the backup.

The backup will be named db1.table1_YYYMMDD_HHMMSS

The algorithm itself is fine (+1) !!!

share|improve this answer
add comment

not fully sure this is the syntax for mysql, but it should be close:

PREPARE statemt FROM CONCAT('CREATE TABLE ', given_date, ' LIKE db1.table1;');
EXECUTE statemt;
DEALLOCATE PREPARE statemt;

then you can do the same for the insert

Edit by RolandoMySQLDBA

You were close (+1). This is a little closer:

SET @sql = CONCAT('CREATE TABLE ', given_date, ' LIKE db1.table1;');
PREPARE statemt FROM @sql;
EXECUTE statemt;
DEALLOCATE PREPARE statemt;
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.