BACKGROUND
Prior to Oracle Database 26ai, inserting multiple rows into a table with one statement was possible but… not quite easy! There were many ways to do this, including :
- Using
INSERT ALL - Using the
INSERT... SELECT - Using
UNION ALL - Using
CONNECT BY
All these methods worked for inserting multiple rows into a table at the same time; however, (with the exception of the INSERT... SELECT) they are not always very intuitive. The VALUES constructor extension makes things a lot easier. Oracle Database 26ai already provides us with NON-POSITIONAL INSERTS. This is yet another feature that is greatly helpful and useful for adding multiple rows to a table.
INTRODUCTION
In Oracle database 26ai, the VALUE constructor has been extended so that you can now insert several rows in a single insert statement. It can be used in a wide variety of ways. With this new clause, multiple rows of data can be generated “on the fly” and there’s no dependency on pre-existing tables. Oracle supports the VALUES clause for the SELECT, INSERT, and MERGE statements. With this new clause, developers can write less code and improve code readability.
USAGE
Starting with Oracle 23c/26ai, the VALUE constructor has been extended so that you can now insert several rows in a single INSERT statement.
Syntax
(VALUES (expression_list) [, (expression_list) ...]) name (column_list)It can be used for various scenarios:
EXAMPLES
I will be using the fictional teams table to show some examples. The code below creates the table.
CREATE TABLE IF NOT EXISTS teams
(
team_id NUMBER
,team_name VARCHAR(32)
) ;
Table TEAMS created.
Elapsed: 00:00:00.015Inserting Multiple Rows Into a Table
INSERT… VALUES
Using the values clause, we can insert multiple rows at once, separating them using a comma.
INSERT INTO teams (team_id, team_name)
VALUES
( 1, 'Liverpool FC')
,(2, 'Barcelona FC')
,(3,'Real Madrid CF')
,(4, 'AC Milan')
,(5, 'Inter Miami');
5 rows inserted.
Elapsed: 00:00:00.018INSERT… SELECT… VALUES
We can query from a values expression as shown below :
SELECT team_id, team_name
FROM (
VALUES
( 1, 'Liverpool FC')
,(2, 'Barcelona FC')
,(3,'Real Madrid CF')
,(4, 'AC Milan')
,(5, 'Inter Miami')
) AS teams (team_id, team_name) ;
The results must be aliased and have column names as highlighted above.
Thus, we can insert into the table using the results of the above SELECT statement.
DELETE FROM teams ;
...
INSERT INTO teams
SELECT team_id, team_name
FROM (
VALUES
( 1, 'Liverpool FC')
,(2, 'Barcelona FC')
,(3,'Real Madrid CF')
,(4, 'AC Milan')
,(5, 'Inter Miami')
) AS teams (team_id, team_name) ;
5 rows inserted.
Elapsed: 00:00:00.014
INSERT using a WITH clause
We can use the values clause in a WITH statement either to generate values or to insert rows into a table.
WITH teams(team_id, team_name) AS
(
VALUES
( 1, 'Liverpool FC')
,(2, 'Barcelona FC')
,(3,'Real Madrid CF')
,(4, 'AC Milan')
,(5, 'Inter Miami')
)
SELECT team_id, team_name
FROM teams ;
TEAM_ID TEAM_NAME
------- ----------------
1 Liverpool FC
2 Barcelona FC
3 Real Madrid CF
4 AC Milan
5 Inter Miami
Elapsed: 00:00:00.003
5 rows selected.
....
DELETE FROM teams ;
INSERT INTO teams
WITH teams(team_id, team_name) AS
(
VALUES
( 1, 'Liverpool FC')
,(2, 'Barcelona FC')
,(3,'Real Madrid CF')
,(4, 'AC Milan')
,(5, 'Inter Miami')
)
SELECT team_id, team_name
FROM teams ;
5 rows inserted.
Elapsed: 00:00:00.005
Creating a table using a SELECT statement
We can create a table using a select statement that reads the values generated by the VALUES clause
DROP TABLE IF EXISTS TEAMS ;
--
CREATE TABLE IF NOT EXISTS teams AS
SELECT team_id, team_name
FROM (
VALUES
( 1, 'Liverpool FC')
,(2, 'Barcelona FC')
,(3,'Real Madrid CF')
,(4, 'AC Milan')
,(5, 'Inter Miami')
) AS teams (team_id, team_name) ;
Table TEAMS created.
Elapsed: 00:00:00.015
...
SELECT * FROM teams ;
TEAM_ID TEAM_NAME
------- ----------------
1 Liverpool FC
2 Barcelona FC
3 Real Madrid CF
4 AC Milan
5 Inter Miami
Elapsed: 00:00:00.021
5 rows selected. Update Values in a table
By using the VALUES clause, we can define a set of rows on the fly and use them to update existing records in a table. The example below demonstrates how this works.
SELECT * FROM teams ;
TEAM_ID TEAM_NAME
------- ----------------
1 Liverpool FC
2 Barcelona FC
3 Real Madrid CF
4 AC Milan
5 Inter Miami
Elapsed: 00:00:00.021
5 rows selected.
....
UPDATE TEAMS t
SET t.team_id = t.team_id * u.multiplier
FROM (
VALUES (6)
) u (multiplier)
WHERE t.team_id = 1 ;
1 row updated.
Elapsed: 00:00:00.008
....
SELECT * FROM teams ;
TEAM_ID TEAM_NAME
------- ----------------
6 Liverpool FC
2 Barcelona FC
3 Real Madrid CF
4 AC Milan
5 Inter Miami
Elapsed: 00:00:00.002
5 rows selected. USE IN MERGE STATEMENTS
You can use the VALUES clause to generate the source data for a merge statement, as shown below:
MERGE INTO teams t_target
USING (
VALUES
(6, 'Fulham FC'),
(11, 'Chelsea FC')
) t_source (team_id,team_name)
ON (t_target.team_id = t_source.team_id)
WHEN MATCHED THEN
UPDATE SET t_target.team_name = t_source.team_name
WHEN NOT MATCHED THEN
INSERT
(
team_id
,team_name
)
VALUES
(
t_source.team_id
,t_source.team_name
);
2 rows merged.
Elapsed: 00:00:00.016
SELECT * FROM teams ;
TEAM_ID TEAM_NAME
------- ----------------
11 Chelsea FC
6 Fulham FC
2 Barcelona FC
3 Real Madrid CF
4 AC Milan
5 Inter Miami
Elapsed: 00:00:00.002
6 rows selected.
CONCLUSION
As the above examples showed, the VALUES clause in ORACLE 26ai is very dynamic and can be used in a number of scenarios to ease the generation of data, making code readable and more efficient. The VALUES constructor clause is something I can’t wait to start using, as I have always wished for a simpler way to do multi-row inserts. Hopefully, this blog made this feature easier for you to understand, and you found this new addition to what is already a super feature-rich edition of the database helpful.
Cheers, Harris 🎊