INSERT_ALL

Warning

SingleStore 9.0 gives you the opportunity to preview, evaluate, and provide feedback on new and upcoming features prior to their general availability. In the interim, SingleStore 8.9 is recommended for production workloads, which can later be upgraded to SingleStore 9.0.

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.

SQL

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 cluster 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)