need to copy data from one table to other table with small chunks-CodePudding

I want to copy the data from table T1 to table T2 using.

There are 170 billion records in Table T1.

we tried Using a standard SQL statement like "insert into T2 select * from T1 where date>='01-01-2022' and date'01-02-2022';" is creating severe performance problems or a DB outage.

I don't mind if it takes many hours if I replicate data slowly from T1 to T2 with a limit of 1 record also fine(expecting small chunks).

how to use stored procedures to automate this operation.

Any tool can assist us in copying data into a few rows at a time for inserting.

CodePudding user response：

The stored procedure may look like:

CREATE PROCEDURE copy_by_chunks (
    IN date_start DATETIME,
    IN date_end DATETIME,
    IN chunk_in_hours INT
    )
BEGIN
    REPEAT
        INSERT INTO table2
            SELECT * 
            FROM table1 
            WHERE created_at >= date_start
              AND created_at < LEAST(date_start   INTERVAL chunk_in_hours HOURS, date_end);
        SET date_start = date_start   INTERVAL chunk_in_hours HOURS;
        -- if you want to make a pause between chunks then use (for 1-second pause)
        -- SET date_start = date_start   INTERVAL chunk_in_hours   SLEEP(1) HOURS;
    UNTIL date_start >= date_end END REPEAT;
END

Recommended chunk size is one which provides a copying of 5k-10k rows.

Of course you must check the provided parameters values correctness (neither parameter is NULL, date_start is less than date_end and so on) and break the procedure if they're incorrect. Also you may add copying progress printing.