我需要将100万条记录从一个表中分两批插入到另一个表中，在MYSQL中每个记录为500k

Question

嗨，谁能帮我在MYSQL中插入700万条记录7年(每年百万条记录)在循环中的程序，

我需要为每一批插入500,000。

表"Archive_data“中有数据，需要在"Stg_table”中插入

归档数据有年销售量，我想在MYSQL中写一个循环，按年循环，每批500K插入

我试过了

insert into SDL_Stg_Bill_Details
select SDL_Id, Rec_Is_Processed, concat(Bill_Header_Key,'_',Row_Num), Bill_Header_Key,Row_Num from (
SELECT SDL_Id, Rec_Is_Processed, Bill_Details_Key, Bill_Header_Key,
 ROW_NUMBER() OVER(partition by Bill_Header_Key order by SDL_Id ) Row_Num
FROM PANTALOONS_SOLUS_PROD.SDL_Stg_Bill_Details_Archive 
where EXTRACT(YEAR_MONTH FROM Bill_Date) in ('201406',
'201407',
'201408',
'201409',
'201410') ff

如果一次尝试7 M记录，则会出现锁定等待超时错误

提前感谢

Answer 1

不确定这是否是你想要的，但我希望通过一些调整，它可能会对你有所帮助：

DELIMITER $
CREATE PROCEDURE CopyDataInBatch()
BEGIN
    DECLARE x  INT;
    DECLARE y  INT;
    SET x = 1;
    SET y = x + 10000;
    the_loop :  LOOP
        IF  x > 1000000 THEN  /* Use the number of rows you wanted to copy*/
            LEAVE  the_loop;
        END  IF;
        INSERT INTO table_in_which_copying (col_name_1, col_name_2)
        SELECT (col_name_1, col_name_2) FROM table_from_which_copying
        ORDER BY (col_name_1)
        LIMIT x, y;
        SET  x = x + 10000;
        SET  y = y + 10000;
        SELECT "Copied 10000 rows"; /* Only for testing... better remove it(?)*/
    END LOOP;
END $ 
DELIMITER ;