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:
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
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:
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!
No comments yet... Be the first to leave a reply!