Background
Oracle AI Database 26ai provides new, easier and self documenting syntax for insert statements: Non-positional Inserts.
This makes use of the INSERT INTO SET and BY NAME clauses
The SET clause works similarly to the existing set clause for update statements, and the by name clause makes it easier to insert the results of a subquery as it matches columns by their name rather than their position in the insert and select lists.
Historically, INSERT INTO using the VALUES clause and SELECT lists required matching columns positionally. Matching hundreds of columns in this manner can become cumbersome. Matching columns by their name eases the process of writing the insert statement.
In this blog post, I will attempt to explain with examples how you can use the above 2 mentioned clauses.
USAGE
INSERT INTO SET
Consider the below example on using the insert into set clause.
We create the below Fictional table :
DROP TABLE IF EXISTS bmore_cops;
CREATE TABLE bmore_cops
( cop_id NUMBER(4,0) PRIMARY KEY,
cop_name VARCHAR2(32),
rank VARCHAR2(9),
boss VARCHAR2(32),
hiredate DATE,
sal NUMBER(7,2),
overtime NUMBER(7,2),
dept VARCHAR2(32)
)
;
Table BMORE_COPS created.Prior to 26ai we can insert data into a table using the INSERT INTO… VALUES clauses such as;
INSERT INTO bmore_cops (
cop_id,
cop_name,
rank,
boss,
hiredate,
sal,
overtime,
dept
) VALUES (
1,
'James McNulty',
'Detective',
'Lt Daniels',
TO_DATE('22-APR-2002', 'DD-MON-YYYY'),
1500,
300,
'Homicide'
);
1 row inserted.
Commit;
Commit complete.
This works well but requires that the columns and their values are specified in the same order, or if the column names are omitted the values must match the order of the columns in the table as it was created.
With Oracle Database 26ai, The INSERT INTO… SET can be used as below ;
INSERT INTO bmore_cops
SET
cop_id = 2
,cop_name = 'Lester Freamont'
,rank = 'Detective'
,boss = 'Lt Daniels'
,hiredate = TO_DATE('22-APR-1984','DD-MON-YYYY')
,sal = 3200
,overtime = 700
,dept = 'Homicide'
;
1 row inserted.
Commit;
Commit complete.
SQL> SELECT * FROM bmore_cops;
COP_ID COP_NAME RANK BOSS HIREDATE SAL OVERTIME DEPT
______ _______________ _________ __________ _________ ____ ________ ________
1 James McNulty Detective Lt Daniels 22-APR-02 1500 300 Homicide
2 Lester Freamont Detective Lt Daniels 22-APR-84 3200 700 Homicide
As seen above, the INSERT INTO… SET clause is akin to the UPDATE… SET statement. It makes it easier and clearer to see what value is inserted into the column and eliminates the need to follow the exact order of the columns. This is easier to write for clarity and is more readable for others who didn’t write this code (immediately obvious what column accepts what value) making it easier to maintain subsequently.
Multiple Rows
We can also insert multiple rows at once using the INSERT INTO… SET clause such as;
INSERT INTO bmore_cops
SET
( cop_id=3 ,cop_name =q'[William "Bunk" Westmoreland]',rank = 'Detective' ,boss = 'Lt Daniels',hiredate = TO_DATE('16-SEP-1998','DD-MON-YYYY'),sal=2600,overtime=400,dept='Homicide'),
( cop_id=4 ,cop_name ='Kima Greggs',rank = 'Detective' ,boss = 'Lt Daniels',hiredate = TO_DATE('12-JUN-1999','DD-MON-YYYY'),sal=1700,overtime=300,dept='Homicide'),
( cop_id=5 ,cop_name ='Roland Pryzbylewski',rank = 'Detective' ,boss = 'Lt Daniels',hiredate = TO_DATE('13-NOV-2002','DD-MON-YYYY'),sal=5000,overtime=900,dept='Homicide')
;
SQL> select * from bmore_cops;
COP_ID COP_NAME RANK BOSS HIREDATE SAL OVERTIME DEPT
______ ___________________________ _________ __________ _________ ____ ________ ________
1 James McNulty Detective Lt Daniels 22-APR-02 1500 300 Homicide
2 Lester Freamont Detective Lt Daniels 22-APR-84 3200 700 Homicide
3 William "Bunk" Westmoreland Detective Lt Daniels 16-SEP-98 2600 400 Homicide
4 Kima Greggs Detective Lt Daniels 12-JUN-99 1700 300 Homicide
5 Roland Pryzbylewski Detective Lt Daniels 13-NOV-02 5000 900 Homicide
Why use this?
The main benefits of using the insert into set clause include:
- Easier readability
- Self-documentation
- Allows logical grouping
INSERT BY NAME
Consider the example below on using the insert into by name clause.
In order to demonstrate use of the insert into by name clause, We create another example table with data that relates back to the bmore_cops table
CREATE TABLE bmore_inmates (
inmate_id NUMBER PRIMARY KEY
,inmate_name VARCHAR2(32)
,facility VARCHAR2(32) DEFAULT 'JESSUP'
,arrested_by NUMBER
,CONSTRAINT bmore_inmates_fk FOREIGN KEY(arrested_by) references bmore_cops(cop_id)
);
INSERT INTO bmore_inmates
SET (inmate_id = 1, inmate_name = q'[Marquis "Bird" Hilton]', arrested_by = 3),
(inmate_id = 2, inmate_name = 'Avon Barksdale', arrested_by = 1),
(inmate_id = 3, inmate_name = 'Nicholas Sobotka', arrested_by = 2);
CREATE table bmore_arrests (
arrest_id NUMBER PRIMARY KEY
,cop_id NUMBER
,inmate_id NUMBER
,facility VARCHAR2(32)
,arrest_date DATE DEFAULT SYSDATE
,CONSTRAINT bmore_arrests_fk1 FOREIGN KEY (cop_id) references bmore_cops(cop_id)
,CONSTRAINT bmore_arrests_fk2 FOREIGN KEY (inmate_id) references bmore_inmates(inmate_id)
);
The goal is to insert data into the bmore_arrests table using data from bmore_cops and bmore_inmates.
In older oracle versions we would write a query like ;
SQL> INSERT INTO bmore_arrests
2 SELECT
3 rownum
4 ,c.cop_id
5 ,i.inmate_id
6 ,i.facility
7 ,NULL
8 FROM
9 bmore_cops c
10* INNER JOIN bmore_inmates i ON c.cop_id = i.arrested_by ;
3 rows inserted.
SQL>
SQL>
SQL>
SQL> select * from bmore_arrests
2* ;
ARREST_ID COP_ID INMATE_ID FACILITY ARREST_DATE
_________ ______ _________ ________ ___________
1 3 1 JESSUP
2 1 2 JESSUP
3 2 3 JESSUP
SQL>
Notice how the select list has to line up with the order of the columns in the table. With the new syntax in oracle 26ai, this can be re-written as :
SQL> INSERT INTO bmore_arrests
2 BY NAME
3 SELECT
4 c.cop_id cop_id
5 ,i.inmate_id inmate_id
6 ,rownum arrest_id
7 ,i.facility facility
8 FROM
9 bmore_cops c
10* INNER JOIN bmore_inmates i ON c.cop_id = i.arrested_by ;
3 rows inserted.
SQL>
SQL>
SQL> select * from bmore_arrests;
ARREST_ID COP_ID INMATE_ID FACILITY ARREST_DATE
_________ ______ _________ ________ ___________
1 3 1 JESSUP 13-MAR-26
2 1 2 JESSUP 13-MAR-26
3 2 3 JESSUP 13-MAR-26
SQL>
SQL>
We get the same valid data in the rows as with the before query regardless of having the columns in any order, the insert picked up on the column names (the aliases are not required if the column names match)
What’s the data?
I can get details of what cop arrested what inmate using ;
SQL> SELECT
2 inmate_name
3 ,arrest_date
4 ,cop_name arrested_by
5 FROM bmore_arrests
6 JOIN bmore_cops using(cop_id)
7* JOIN bmore_inmates using(inmate_id) ;
INMATE_NAME ARREST_DATE ARRESTED_BY
_____________________ ___________ ___________________________
Marquis "Bird" Hilton 13-MAR-26 William "Bunk" Westmoreland
Avon Barksdale 13-MAR-26 James McNulty
Nicholas Sobotka 13-MAR-26 Lester Freamont
CONCLUSION
The above blog post examined the basics and overall usage and idea of the new INSERT INTO SET and INSERT INTO BY NAME Clauses In oracle database 26ai. This feature makes it easier to write and maintain complex and/or long insert statements. Here are links to some other posts for further reading up on this.
Gerald Venzl’s blog
oracle base blog
PS: If the examples made you remember a certain show you watched (and loved) please leave a comment/connect with me, seems like we may have a lot in common ! 😉
cheers 🎊