Sign inTry Free

INSERT_ALL

On this page

Inserts an array of records into a table in a single operation.

Provides much better performance than doing singleton inserts in a LOOP.

Syntax

INSERT_ALL("table_name", array);

Arguments

  • table_name: the name of the table to insert the values into

  • array: the array of values

Remarks

  • The current database is used when calling INSERT_ALL, so any tables specified in table_name must reside in that database. Qualified table names, such as database.table, will be treated as unqualified table names.

Example

The stored procedure, t2Slow, performs singleton inserts into table t2, while t2Fast inserts the same values using the INSERT_ALL function.

CREATE TABLE t2 (a INT, b INT);


DELIMITER //
CREATE OR REPLACE PROCEDURE t2Slow(count INT) AS
BEGIN
    FOR i IN 1..count LOOP
        INSERT INTO t2 VALUES (i, i);
    END LOOP;
END //


CREATE OR REPLACE PROCEDURE t2Fast(count INT) AS
DECLARE
    arr ARRAY(RECORD(a INT, b INT)) = CREATE_ARRAY(count);
    x INT;
BEGIN
    FOR i IN 0..(count-1) LOOP
        arr[i] = ROW(i+1, i+1);
    END LOOP;
    x = INSERT_ALL("t2", arr);
end //
DELIMITER ;


-- Insert 1,000,000 rows into t2 using both stored procedures. Note: These query times are based on a workspace hosted on a single machine.
CALL t2Slow(1000000);
Query OK, 0 rows affected (3 min 31.12 sec)


CALL t2Fast(1000000);
Query OK, 0 rows affected (33.13 sec)

Last modified: November 17, 2022

Was this article helpful?

On this page

Was this article helpful?