Multi-Record Insert Statement

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')
.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)

Note that this tip also applies to SQL Server!

Twitter Digg Delicious Stumbleupon Technorati Facebook Email

No comments yet... Be the first to leave a reply!

Leave a Reply

You must be logged in to post a comment.