Multi-Record Insert Statement
Posted: 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:
MySQL:
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:
Note that this tip also applies to SQL Server!
Have fun!
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
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)
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)
Have fun!