In my stored procedure I am calling PREPARE for multiple INSERT IGNORE INTO statements from one database to another, but there is a chance that they dont have the same tables. So all I want to do is to skip that SQL statement and continue to next one if table doesnt exist, i.e. after the PREPARE statement fails.
This is my code:
SET @ins = CONCAT('INSERT IGNORE INTO ', databaseName, '.currentYear SET year = YEAR(now()) + 1;');
PREPARE stmt FROM @ins;
EXECUTE stmt;
SET @ins = CONCAT('INSERT IGNORE INTO ', databaseName, '.table1 SELECT * FROM table1;');
PREPARE stmt FROM @ins;
EXECUTE stmt;
SET @ins = CONCAT('INSERT IGNORE INTO ', databaseName, '.table2 SELECT * FROM table2;');
PREPARE stmt FROM @ins;
EXECUTE stmt;
SET @ins = CONCAT('INSERT IGNORE INTO ', databaseName, '.table3 SELECT * FROM table3;');
PREPARE stmt FROM @ins;
EXECUTE stmt;
and so on.. more than 100 of these statements. So when one of these raises an exception "table doesn't exists" I want to ignore that error and continue to the next statement and so on down the line. How can I achieve that?
Thank you in advance!
I've found a solution and it's actually pretty simple. First step is to declare a CONTINUE HANDLER FOR 1146 (mySQL error code for "Table doesn't exist") Then set a variable to TRUE when it gets the error code (1146). Next step is to simply use If statement before EXECUTE to ask if the variable is set to TRUE or FALSE. If FALSE, EXECUTE stmt. Before each PREPARE statement we need to set our variable to FALSE, in case it was triggered by CONTINUE HANDLER and set to TRUE.
Here is the example code:
DECLARE CONTINUE HANDLER FOR 1146 SET @error = TRUE;
SET @ins = CONCAT('INSERT IGNORE INTO ', databaseName, '.currentYear SET year = YEAR(now()) + 1;');
SET @error = FALSE;
PREPARE stmt FROM @ins;
IF @error = FALSE THEN EXECUTE stmt; END IF;
SET @ins = CONCAT('INSERT IGNORE INTO ', databaseName, '.table1 SELECT * FROM table1;');
SET @error = FALSE;
PREPARE stmt FROM @ins;
IF @error = FALSE THEN EXECUTE stmt; END IF;
SET @ins = CONCAT('INSERT IGNORE INTO ', databaseName, '.table1 SELECT * FROM table1;');
SET @error = FALSE;
PREPARE stmt FROM @ins;
IF @error = FALSE THEN EXECUTE stmt; END IF; etc.
This way we are able to change or add insert statements (for new tables) to our procedure without getting an error if the table doesn't exist in targeted database. Still to be tested further but it gets the job done for now. I hope someone will find this answer useful. Cheers!