Multi-Record Insert Statement

April 17, 2013 0 Comments

The MySQL and Oracle databases have a cool feature in that we can insert more than one record into a table at the same time using a single INSERT statement.

Here’s an example using Oracle:

SQL> CREATE TABLE more_than_one (col1 VARCHAR(1));

Table created.

SQL> INSERT ALL

. 2    INTO more_than_one (col1) VALUES ('A')

. 3    INTO more_than_one (col1) VALUES ('B')

. 4    INTO more_than_one (col1) VALUES ('C')

. 5  SELECT * FROM dual;

3 rows created.

SQL> SELECT * FROM more_than_one;

C

-

A

B

C

And here is an example using MySQL:

MySQL> CREATE TABLE more_than_one (col1 VARCHAR(1));

Query OK, 0 rows affected (0.11 sec)

MySQL> INSERT INTO more_than_one VALUES ('A'), ('B'), ('C');

Query OK, 3 rows affected (0.00 sec)

Records: 3  Duplicates: 0  Warnings: 0

MySQL> SELECT * FROM more_than_one;

+------+

| col1 |

+------+

| A    |

| B    |

| C    |

+------+

3 rows in set (0.00 sec)

The Vertica INSERT statement does not have the built in capability to perform a multi-record inserts, but we can simulate this functionality by combining SELECT statements with the UNION operator.

Here is an example:

dbadmin=> CREATE TABLE more_than_one (col1 VARCHAR(1));

CREATE TABLE

dbadmin=> INSERT INTO more_than_one

dbadmin->   SELECT 'A'

dbadmin->    UNION ALL

dbadmin->   SELECT 'B'

dbadmin->    UNION ALL

dbadmin->   SELECT 'C';

OUTPUT

--------

3

(1 row)

dbadmin=> SELECT * FROM more_than_one;

col1

------

C

A

B

(3 rows)