MySQL Backup Table if it Exists

Question

I am trying to write a script that will copy all the data in table a to table b if table a exists. Table b is the exact same structure as table a would be if it exists, though it may not. I am able to copy using the following statement: INSERT INTO 'B' SELECT * FROM 'A', but I don't know where to use IF EXISTS, or if I even can to determine if I an perform the insertion. I am trying to do this in SQL only as it will be run through as a .sql script from the command line.

Answer 1

You can do so by performing the following:

select count(*) from my_tables where table_name='b'; 
If count>0 then 
update b...; 
else 
create table b; 
insert into my_tables(table_name) values('b'); 
insert into b...; 
end if;

Answer 2

MySQL only:

DROP PROCEDURE IF EXISTS myupdate;
DELIMITER //
CREATE PROCEDURE myupdate ()
BEGIN 
DECLARE found VARCHAR(64);
SET found = (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = Database() AND TABLE_NAME = 'A');
IF found = 'types' THEN
   INSERT INTO B SELECT * FROM A;
   SELECT 'A into B';
ELSE 
   SELECT 'A not found';
END IF;
END;//
DELIMITER ;
CALL myupdate();
DROP PROCEDURE myupdate;

Expand to you're liking comparing the column definition in INFORMATION_SCHEMA.COLUMNS for A & B if you need finer control.

Answer 3

I have accepted Wrikken's answer but am using this as my final code. I need to reuse the procedure he provided for multiple tables, so I modified it slightly. It makes the assumption that the backup table has already been created.

DROP PROCEDURE IF EXISTS tableUpdate;
DELIMITER //
CREATE PROCEDURE tableUpdate(name VARCHAR(32))
BEGIN
DECLARE cnt tinyint(1);
DECLARE btable VARCHAR(36);
SET btable = CONCAT(name,'BAK');
SET cnt = (SELECT COUNT(TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'TAA' AND TABLE_NAME = name);
IF cnt > 0 THEN
   SET @q:= CONCAT('INSERT INTO ',CONCAT(btable,CONCAT(' SELECT * FROM ',name)));
   PREPARE stmt FROM @q;
   EXECUTE stmt;
   COMMIT;
ELSE
   SELECT 'No update necessary.';
END IF;
END;//
DELIMITER ;
CALL tableUpdate('A');
DROP PROCEDURE tableUpdate;