Important

Helios features are now enabled during weekly update windows and are no longer directly tied to SingleStore engine releases. Refer to the release notes to view the latest features available in your Helios cluster.

INSERT_ALL

The INSERT_ALL 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 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)

Last modified:

Was this article helpful?