Copy codeThe Code is as follows:
DELIMITER $
USE 'servant _ 591up' $
Drop procedure if exists 'SP _ move_data '$
Create procedure 'SP _ move_data '()
BEGIN
DECLARE v_exit int default 0;
DECLARE v_spid BIGINT;
DECLARE v_id BIGINT;
DECLARE I INT DEFAULT 0;
DECLARE c_table INT;
DECLARE v_UniqueKey VARCHAR (57 );
DECLARE v_TagCatalogId INT;
DECLARE v_RootCatalogId INT;
DECLARE v_UserId BIGINT;
DECLARE v_QuestionId CHAR (36 );
DECLARE v_CorrectCount INT;
DECLARE v_ErrorCount INT;
DECLARE v_LastIsCorrect INT;
DECLARE v_LastAnswerXML text charset utf8;
DECLARE v_TotalCostTime INT;
DECLARE v_Reviews VARCHAR (200) CHARSET utf8;
DECLARE v_AnswerResultCategory INT;
DECLARE v_LastCostTime INT;
DECLARE v_LastAnswerTime DATETIME;
DECLARE v_IsPublic INT;
DECLARE v_SUBJECT INT;
DECLARE v_TotalCount INT;
DECLARE v_AnswerMode SMALLINT (6 );
DECLARE v_ExerciseWeight FLOAT;
DECLARE c_ids cursor for select UniqueKey, delimiter, delimiter, UserId, QuestionId, CorrectCount, ErrorCount, delimiter, LastAnswerXML, delimiter, Reviews, counts, counts, LastAnswerTime, IsPublic, SUBJECT, TotalCount, answerMode, ExerciseWeight FROM ol_answerresult_56;
Declare continue handler for not found set v_exit = 1;
OPEN c_ids;
REPEAT
FETCH c_ids INTO v_UniqueKey, primary, primary, v_UserId, primary, v_CorrectCount, v_ErrorCount, primary, v_Reviews, primary, v_IsPublic, v_SUBJECT, v_TotalCount, v_AnswerMode, v_ExerciseWeight;
IF v_exit = 0 THEN
SET @ vv_id = v_id;
Select mod (v_UserId, 100) INTO c_table;
SET @ SQL _CONTEXT =
CONCAT ('insert INTO new_answerresult _',
C_table ,'
(UniqueKey, delimiter, delimiter, UserId, QuestionId, CorrectCount, ErrorCount, delimiter, LastAnswerXML, TotalCostTime, Reviews, counts, counts, LastAnswerTime, IsPublic, SUBJECT, TotalCount, AnswerMode, callback) values (',
''', V_UniqueKey ,'''',',',
V_TagCatalogId ,',',
V_RootCatalogId ,',',
V_UserId ,',',
''', V_QuestionId ,'''',',',
V_CorrectCount ,',',
V_ErrorCount ,',',
V_LastIsCorrect ,',',
''', V_LastAnswerXML ,'''',',',
V_TotalCostTime ,',',
''', REPLACE (IFNULL (v_Reviews ,''),'''',''),'''',',',
V_AnswerResultCategory ,',',
V_LastCostTime ,',',
''', V_LastAnswerTime ,'''',',',
V_IsPublic ,',',
V_SUBJECT ,',',
V_TotalCount ,',',
V_AnswerMode ,',',
V_ExerciseWeight ,')');
Prepare stmt from @ SQL _CONTEXT;
Execute stmt;
Deallocate prepare stmt;
End if;
SET I = I + 1;
#100
# If mod (I, 100) = 0 THEN COMMIT;
# End if;
UNTIL v_exit = 1
End repeat;
CLOSE c_ids;
# COMMIT;
END $
DELIMITER;