I am in trouble coding a store procedure as shown below
CREATE DEFINER=`dbcomexuser`@`%` PROCEDURE `clearance_imp`(IN CAPIfilename VARCHAR(12))
BEGIN
DECLARE `_rollback` BOOL DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET `_rollback` = 1;
DECLARE _refID INT DEFAULT 0;
START TRANSACTION;
SELECT MAX(imp_id) + 1 INTO _refID FROM temp_imp;
INSERT INTO filenamedt (filename, filedate) VALUES (CAPIfilename, now());
INSERT INTO imp__description (imp_id, imp_description) (SELECT imp_id, TRIM(descricaoProduto) FROM temp_imp);
TRUNCATE TABLE temp_imp;
ALTER TABLE temp_imp AUTO_INCREMENT = _refID;
IF `_rollback` THEN
ROLLBACK;
ELSE
COMMIT;
END IF;
END
The main issue is that MySQL Workbench outputs that there is an error at line:
ALTER TABLE temp_imp AUTO_INCREMENT = _refID;
But I can't figure out what is going on.
The fact is that: I am loading my database from hundreds of files and data is being stored in many different tables.
After I load each file, I trucate temporary table, because it is faster than use delete, so I load another file. When I trucate table, MySQL start from 0 the auto increment number, but I need last int number to use it again as I am using it as a primary key.
Does someone can help me?