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?