Introduction
Oracle
Database 23ai introduces a major evolution of the INSERT statement, modernizing
one of the most frequently used DML operations in Oracle SQL. These
enhancements are designed to make data insertion clearer, safer, and more
flexible, especially in environments with wide tables, evolving schemas, and
bulk data loads. New capabilities such as INSERT … SET, INSERT … BY NAME,
multi-row inserts using the VALUES clause, and support for non-numeric column
assignments in SET-based inserts reduce reliance on column order, simplify SQL
syntax, and help prevent common data-mapping errors. Together, these features
significantly improve code readability and developer productivity while
aligning Oracle SQL more closely with modern database standards.
Prerequisites
To
follow along with this blog and reproduce the examples, the following
environment and database objects were used:
- Oracle
Database 23ai Free, running on a VirtualBox Appliance,
with a database version 23.9 or higher.
- The HR sample schema (which exists in Oracle AI Database 23ai Free VirtualBox Appliance)
1. INSERT ... SET
The INSERT
… SET clause allows values to be assigned to columns by name, similar to an UPDATE
statement. This removes the need for a positional column list and makes INSERT
statements easier to read and understand. It is especially useful for tables
with many columns or frequently changing schemas. By explicitly naming columns,
the risk of errors caused by column order mismatches is greatly reduced. Only
specified columns are populated, while others use default values or remain NULL.
This approach improves maintainability and clarity in both application code and
ETL processes.
Example:
INSERT INTO jobs
SET
job_id = 'DATA_SCI',
job_title = 'Data Scientist',
min_salary = 8000,
max_salary = 20000;Before
insert
In the following
image, I show both the traditional and the new
insert methods.
2. INSERT … BY NAME
This clause enables Oracle to map columns from a SELECT subquery to the target table based on name, not position.
It
entirely removes the strict positional dependency required by traditional INSERT
statements. Developers can now freely reorder columns in the SELECT list
without breaking the DML operation. This makes subquery-based inserts
significantly safer against schema evolution. It is especially valuable in
complex ETL workflows and data migrations where source and target structures
may differ. BY NAME also improves readability by allowing the use of meaningful
column aliases for mapping. Overall, this feature enhances flexibility and
greatly simplifies maintenance.
Example:
In this example, I created a simplified target table where the column order does not match the logical order typically found in the source EMPLOYEES table. Specifically, I defined the department_id and salary columns in a different sequence to simulate a common real-world scenario, such as loading data into a staging or temporary table.
CREATE TABLE new_hires (
employee_id NUMBER,
last_name VARCHAR2(25),
department_id NUMBER,
salary NUMBER
);
INSERT INTO new_hires (employee_id, last_name, salary, department_id)
BY NAME
SELECT
employee_id,
last_name,
department_id,
salary
FROM employees
WHERE
hire_date >= DATE '2015-01-01'
AND ROWNUM <= 5; When the INSERT statement executes, Oracle correctly maps each selected column to the corresponding target column using column names and aliases, even though their order differs between the SELECT list and the table definition.
3. Multi-Row INSERT using VALUES
Oracle 23ai now fully supports the insertion of multiple rows within a single INSERT statement. This eliminates the complexity of previous, proprietary methods such as INSERT ALL or chaining multiple rows with UNION ALL clauses. The new syntax is significantly simpler, more readable, and fundamentally easier to maintain, especially when dealing with smaller reference datasets or test environments. Furthermore, adopting this standard syntax greatly improves code portability for developers who work across various database platforms.
Example:
INSERT INTO regions (region_id, region_name)
VALUES
(60, 'North America'),
(70, 'South America'),
(80, 'Antarctica');
4. Non-Numeric Column Assignment in SET
The INSERT ... SET clause provides support across all SQL data types. This includes standard types like character strings (VARCHAR2), numeric values, and DATE. This broad compatibility ensures a consistent, non-positional assignment method can be deployed across the entire database schema.
Example: Assigning a DATE Value to the EMPLOYEES Table
I used the HR schema's EMPLOYEES table to demonstrate assigning a value to the
non-numeric HIRE_DATE column:
Conclusion
The
new INSERT features in Oracle 23ai represent a meaningful shift toward more
expressive and developer-friendly SQL. By eliminating rigid positional column
requirements, simplifying multi-row inserts, and allowing flexible column
assignments through SET-based syntax, Oracle reduces complexity in everyday
data-loading operations. These enhancements not only make INSERT statements
easier to write and maintain but also improve reliability when working with
changing schemas and large datasets. As part of Oracle’s broader SQL
modernization effort, these INSERT improvements lay a strong foundation for
building cleaner, more efficient, and more maintainable database applications.
