Multi-Record Insert Statement

Moderator: NorbertKrupa

Post Reply
User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1775
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Multi-Record Insert Statement

Post by JimKnicely » Tue Apr 16, 2013 9:06 pm

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

Oracle:

Code: Select all

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
MySQL:

Code: Select all

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 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:

Code: Select all

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!

Have fun!
Jim Knicely

Image

Note: I work for HPE. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “Vertica Tips, Lessons and Examples”