Oracle Database 23.9 implements the non-positional INSERT statement. In my social media posts, I called out that Oracle Database is the first and only database to support this functionality, and by that, I mean not only the SET clause but the entirety of that feature set. There is a lot that can be done now, and this blog post provides the backstory of how this feature came about, what it does, and when you should use it.
Table of Contents
- Back story
- What syntax should it be?
- What about the SQL standard and other databases?
- The VALUES clause
- The SET clause
- Benefits of INSERT INTO … SET
- What about INSERT INTO … SELECT?
- Rigorous column list
- What’s next?
- Closing comments
Back story
My journey with non-positional INSERT statement started back in September 2022 with this Twitter post from Morten Braten while presenting new features coming to Oracle Database 23c:
It didn’t take long, and one of my fellow APEX colleagues, Patrick Wolf, chimed in as well:
The requirement was rather simple: Can we have an INSERT statement that clearly describes which column and value belong together?
During internal discussions that followed these tweets, Patrick provided nice examples and thoughts. Today, one finds code as such quite often:
Expand/Collapse
insert into wwv_flow_list_items (
id,
list_id,
flow_id,
list_item_type,
list_item_status,
item_displayed,
list_item_display_sequence,
list_item_link_text,
list_item_link_target,
list_item_current_for_pages,
list_item_current_type )
values (
p_entry_id,
p_list_id,
p_flow_id,
'LINK',
'PUBLIC',
'BY_DEFAULT',
s,
p_name,
l_target,
l_current_for_pages,
l_target_type );
As soon as an insert statement contains literals, the mental link to a column name is lost. With variable names, it’s ok, but still not great if they are not equal, for example. In this example most columns are mapped to a variable with a similar name, which makes reading the insert statement a little bit easier, but still, if a developer wants to add a new column and doesn’t just want to append it at the end, he first adds it to the COLUMNs section and then he has to search for the previous column in the VALUES section. Good luck if the variable is named differently or uses a literal, because in that case you have to start counting the column position. With an UPDATE like syntax everything is already in place.
Patrick Wolf, 11th of October 2022
Patrick’s points were all valid and good. If we were to use an UPDATE-like syntax instead, the above query would look like something like this:
Expand/Collapse
insert into wwv_flow_list_items
set id = p_entry_id,
list_id = p_list_id,
flow_id = p_flow_id,
list_item_type = 'LINK',
list_item_status = 'PUBLIC',
item_displayed = 'BY_DEFAULT',
list_item_display_sequence = s,
list_item_link_text = p_name,
list_item_link_target = l_target,
list_item_current_for_pages = l_current_for_pages,
list_item_current_type = l_target_type;
Such syntax would drastically increase the readability of the statement as it is immediately obvious which target column and input source belong together. Additionally, it also makes maintaining the statement a lot easier, as adding, rearranging, or removing a column runs no risk of changing the wrong positional column in the INSERT part or the wrong value in the VALUES clause. The INSERT statement would become non-positional concerning its column/value assignments.
What syntax should it be?
In my old development days before Oracle, dynamic SQL statement generation was not an uncommon task. I wrote several algorithms that dealt with INSERT and UPDATE statement generation, and I was always annoyed that I needed to keep track of the operation itself (INSERT or UPDATE) to decide whether to construct two lists with columns and values for INSERT or one list with column = value for UPDATE. So when thinking about this new feature, I was very adamant that this new syntax should not just be UPDATE-like syntax, but we should reuse the SET clause from the UPDATE statement in its entirety. That way, SQL syntax generators can construct the same column/value assignment list regardless of whether the operation is an INSERT or UPDATE statement. So application code can be simplified, from:
<operation> <target table>
< <column list insert> VALUES <values list insert> >
| < SET <column and value list> [ WHERE <where clause> ] >
To just:
<operation> <target table>
SET <column and value list>
[ WHERE <where clause> ]
Another benefit of reusing the same syntax, i.e., the SET clause, is that it will come more easily to SQL newbies, as there is only one syntax to learn rather than two. Using the SET clause seemed the right course of action, but we weren’t done just yet.
What about the SQL standard and other databases?
While reviewing the SET syntax proposal, we naturally explored whether the SQL standard offered alternative methods for inserting new rows into a table or if existing constructs conflicted with the proposed SET clause. Our findings confirmed that the standard relies solely on the VALUES clause for this purpose. This sparked my curiosity: in nearly 40 years of the SQL standard’s existence, was there never an alternative approach to inserting values? Or, perhaps, was an alternative tried but abandoned for good reason? Were we at risk of repeating past mistakes? Leveraging my role on the SQL Standard Committee, I delved into historical versions of the standard and consulted with fellow committee members. To my surprise, I found no evidence of alternative mechanisms. The INSERT INTO ... VALUES construct has been a cornerstone of the standard from its inception, with no evidence of initiatives to enhance it.
The SQL standard is one thing, but there are, of course, also other SQL implementations, some of which do not follow the standard much. So next, we looked at some of these implementations, and indeed, a few had already discovered the INSERT INTO ... SET clause as well. MySQL, for example, provides that construct since at least 5.7 (look for SET assignment_list):
Expand/Collapse
mysql> INSERT INTO employees
-> SET employee_id = 210,
-> first_name = 'Gerald',
-> last_name = 'Venzl',
-> salary = 1000,
-> job_id = 3;
Query OK, 1 row affected (0.003 sec)
mysql> select * from employees;
+-------------+------------+-----------+--------+--------+
| employee_id | first_name | last_name | salary | job_id |
+-------------+------------+-----------+--------+--------+
| 210 | Gerald | Venzl | 1000 | 3 |
+-------------+------------+-----------+--------+--------+
1 row in set (0.001 sec)
However, most of the implementations we looked at do not provide the SET clause or similar functionality as of today. But that’s not to say that they never thought about it. When discussing the non-positional INSERT idea within the SQL Standard Committee, Peter Eisentraut from the Postgres community provided me with a pointer to their message archive where the idea of a SET clause and alternative syntax had also been discussed: https://www.postgresql.org/message-id/flat/385970D7-37BF-4D23-A5FD-525BBE18849E%40internetnz.net.nz
So it looked like we were onto something here. Users had aired their annoyance with the VALUES clause, something that was there from the beginning of SQL and probably never looked back at again, and other implementations had provided or at least discussed similar alternatives to overcome the downsides of VALUES.
So that’s it then, right? Just use the SET clause instead of the VALUES clause and you are good, right? RIGHT? Well, turns out it is not quite that simple.
The VALUES clause
Most people don’t actually know this, but VALUES is not a keyword of the INSERT statement. VALUES is an operator on its own. The SQL standard defines VALUES as a query expression that specifies a set of <row value expression>s to be constructed into a table, also known as the table value constructor. In other words, VALUES is a standalone construct that can be used to generate table values. Hence why in Postgres, you can do this:
Expand/Collapse
postgres=# VALUES (1, 2, 3), (4, 5, 6);
column1 | column2 | column3
---------+---------+---------
1 | 2 | 3
4 | 5 | 6
(2 rows)
And in Oracle Database (and Postgres), you can do this:
Expand/Collapse
SQL> SELECT *
FROM (
VALUES (1, 2, 3), (4, 5, 6)
) t (a, b, c);
A B C
____ ____ ____
1 2 3
4 5 6
And because you can generate multiple rows with multiple columns using VALUES, as just demonstrated, you can also do this:
Expand/Collapse
SQL> INSERT INTO employees (employee_id, first_name, last_name, salary, job_id)
VALUES (210, 'Gerald', 'Venzl', 1000, 3),
(220, 'Sepp', 'Forcher', 2000, 3),
(230, 'Herbert', 'Prohaska', 5000, 5);
3 rows inserted.
SQL> select * from employees;
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY JOB_ID
______________ _____________ ____________ _________ _________
210 Gerald Venzl 1000 3
220 Sepp Forcher 2000 3
230 Herbert Prohaska 5000 5
That is, insert three rows with one operation. And again, this works because VALUES is its own construct that generates what essentially is a virtual table that serves as an input to the INSERT statement, just like a INSERT INTO ... SELECT ... FROM would do. And because the result is a virtual table, you can take the same VALUES clause and SELECT ... FROM () from it. The only additional syntax you need is for the FROM clause to define a table alias (my_tab) and the column names (employee_id, first_name, last_name, salary, job_id):
Expand/Collapse
SQL> SELECT *
FROM (
VALUES (210, 'Gerald', 'Venzl', 1000, 3),
(220, 'Sepp', 'Forcher', 2000, 3),
(230, 'Herbert', 'Prohaska', 5000, 5)
)
my_tab (employee_id, first_name, last_name, salary, job_id);
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY JOB_ID
______________ _____________ ____________ _________ _________
210 Gerald Venzl 1000 3
220 Sepp Forcher 2000 3
230 Herbert Prohaska 5000 5
You can insert multiple rows in one go with many other databases. MySQL, for example:
Expand/Collapse
mysql> INSERT INTO employees (employee_id, first_name, last_name, salary, job_id)
-> VALUES (210, 'Gerald', 'Venzl', 1000, 3),
-> (220, 'Sepp', 'Forcher', 2000, 3),
-> (230, 'Herbert', 'Prohaska',5000, 5);
Query OK, 3 rows affected (0.002 sec)
Records: 3 Duplicates: 0 Warnings: 0
But given that MySQL supports the SET clause too, how does it cope with allowing multiple rows in one INSERT operation when using it?
Expand/Collapse
mysql> INSERT INTO employees
-> SET (employee_id = 210, first_name = 'Gerald', last_name = 'Venzl', salary = 1000, job_id = 3),
-> (employee_id = 220, first_name = 'Sepp', last_name = 'Forcher', salary = 2000, job_id = 3),
-> (employee_id = 230, first_name = 'Herbert', last_name = 'Prohaska', salary = 5000, job_id = 5);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(employee_id = 210, first_name = 'Gerald', last_name = 'Venzl', salary = 100' at line 2
Apparently, that doesn’t work. Is it just missing an additional () around it, perhaps?
Expand/Collapse
mysql> INSERT INTO employees
-> SET ( (employee_id = 210, first_name = 'Gerald', last_name = 'Venzl', salary = 1000, job_id = 3),
-> (employee_id = 220, first_name = 'Sepp', last_name = 'Forcher', salary = 2000, job_id = 3),
-> (employee_id = 230, first_name = 'Herbert', last_name = 'Prohaska', salary = 5000, job_id = 5)
-> );
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '( (employee_id = 210, first_name = 'Gerald', last_name = 'Venzl', salary = 1' at line 2
No, that doesn’t work either. That’s because, as of today, MySQL does not support multi-row insertion with its SET clause – the clause supports single-row insertion operations only.
This is, of course, a restriction that we didn’t want for INSERT INTO ... SET. The table value constructor is of great value! Why not provide the same capability for the SET clause, regardless of whether you want to insert one or multiple rows? And hence why in Oracle Database you can do that. Let’s take a closer look.
The SET clause
In Oracle Database 23.9 and beyond, to insert a single row, you can use the identical SET clause as you would use with your UPDATE statements:
Expand/Collapse
SQL> INSERT INTO employees
SET employee_id = 210,
first_name = 'Gerald',
last_name = 'Venzl',
salary = 1000,
job_id = 3;
1 row inserted.
If you wanted to insert multiple rows in one go, just think of replacing the VALUES clause with the SET clause. Now remember, to insert multiple rows with VALUES, you put parentheses around each row. This is to clearly indicate the beginning and end of a row, for example:
Expand/Collapse
SQL> INSERT INTO employees (employee_id, first_name, last_name, salary, job_id)
VALUES (210, 'Gerald', 'Venzl', 1000, 3),
(220, 'Sepp', 'Forcher', 2000, 3),
(230, 'Herbert', 'Prohaska', 5000, 5);
3 rows inserted.
The same is true for the SET clause. To clearly mark a row, you have to enclose the row’s values in ():
Expand/Collapse
SQL> INSERT INTO employees
SET (employee_id = 210, first_name = 'Gerald', last_name = 'Venzl', salary = 1000, job_id = 3),
(employee_id = 220, first_name = 'Sepp', last_name = 'Forcher', salary = 2000, job_id = 3),
(employee_id = 230, first_name = 'Herbert', last_name = 'Prohaska', salary = 5000, job_id = 5);
3 rows inserted.
And remember how I said before that I dealt with a lot of SQL generation in my past? That’s why Oracle Database also allows you to put a () around a single row – no need to keep count of how many rows you actually attach to your INSERT operation and whether or not you need to put parentheses around it:
Expand/Collapse
SQL> INSERT INTO employees
SET (employee_id = 210, first_name = 'Gerald', last_name = 'Venzl', salary = 1000, job_id = 3);
1 row inserted.
Benefits of INSERT INTO ... SET
So we talked about why and how we arrived at the SET clause, but let’s dig into the benefits of it when using it.
Readability and maintainability
As mentioned earlier, one of the benefits of non-positional INSERT was to make it clear which column and value belong together. It is not uncommon for tables to have more than a handful of columns. In practice, tables often have 100s or more columns. Likewise, tables often have string-based columns that store several 100 bytes of data. In such scenarios, an INSERT statement can get rather long. Here is a real example of an INSERT statement storing US National Park information:
Expand/Collapse
INSERT INTO parks (park_id, park_code, name, full_name, url, description, designation, latitude, longitude, states, directions_info, directions_url, weather_info, country_id)
VALUES (
'77E0D7F0-1942-494A-ACE2-9004D2BDC59E',
'abli',
'Abraham Lincoln Birthplace',
'Abraham Lincoln Birthplace National Historical Park',
'https://www.nps.gov/abli/index.htm',
'For over a century people from around the world have come to rural Central Kentucky to honor the humble beginnings of our 16th president, Abraham Lincoln. His early life on Kentucky''s frontier shaped his character and prepared him to lead the nation through Civil War. Visit our country''s first memorial to Lincoln, built with donations from young and old, and the site of his childhood home.',
'National Historical Park',
37.5858662,
-85.67330523,
'KY',
'The Birthplace Unit of the park is located approximately 2 miles south of the town of Hodgenville on U.S. Highway 31E South. The Boyhood Home Unit at Knob Creek is located approximately 10 miles northeast of the Birthplace Unit of the park.',
'http://www.nps.gov/abli/planyourvisit/directions.htm', 'There are four distinct seasons in Central Kentucky. However, temperature and weather conditions can vary widely within those seasons. Spring and Fall are generally pleasant with frequent rain showers. Summer is usually hot and humid. Winter is moderately cold with mixed precipitation.',
'USA'
);
In contrast, below is what the statement looks like, leveraging the SET clause. I think you will agree that this statement is much quicker to comprehend and easier to add or remove columns from:
Expand/Collapse
INSERT INTO parks
SET
park_id = '77E0D7F0-1942-494A-ACE2-9004D2BDC59E',
park_code = 'abli',
name = 'Abraham Lincoln Birthplace',
full_name = 'Abraham Lincoln Birthplace National Historical Park',
url = 'https://www.nps.gov/abli/index.htm',
description = 'For over a century people from around the world have come to rural Central Kentucky to honor the humble beginnings of our 16th president, Abraham Lincoln. His early life on Kentucky''s frontier shaped his character and prepared him to lead the nation through Civil War. Visit our country''s first memorial to Lincoln, built with donations from young and old, and the site of his childhood home.',
designation = 'National Historical Park',
latitude = 37.5858662,
longitude = -85.67330523,
states = 'KY',
directions_info = 'The Birthplace Unit of the park is located approximately 2 miles south of the town of Hodgenville on U.S. Highway 31E South. The Boyhood Home Unit at Knob Creek is located approximately 10 miles northeast of the Birthplace Unit of the park.',
directions_url = 'http://www.nps.gov/abli/planyourvisit/directions.htm',
weather_info = 'There are four distinct seasons in Central Kentucky. However, temperature and weather conditions can vary widely within those seasons. Spring and Fall are generally pleasant with frequent rain showers. Summer is usually hot and humid. Winter is moderately cold with mixed precipitation.',
country_id = 'USA';
Non-uniform rows
I have shown before that the table values constructor enables the insertion of multiple rows in one INSERT statement. However, constructing such a statement can become unnecessarily verbose if the row values are not uniform, meaning that the new rows do not provide values for the same columns. Explicit NULL or DEFAULT keywords are needed to guarantee that the intended column positions remain intact:
Expand/Collapse
INSERT INTO jobs (job_id, job_title, min_salary, max_salary)
VALUES ( 3, 'Product Manager', 5000, NULL),
( 4, 'Junior Consultant', NULL, 4000),
( 2, 'Vice President', NULL, NULL);
In contrast, with the SET clause, neither NULL keywords nor target columns need to be specified, making the statement less verbose and adding further to the statement’s readability and maintainability:
Expand/Collapse
INSERT INTO jobs SET
(job_id = 3, job_title = 'Product Manager', min_salary = 5000),
(job_id = 4, job_title = 'Junior Consultant', max_salary = 4000),
(job_id = 5, job_title = 'Vice President');
Grouping of logical entities
Certain data elements are meaningless on their own but meaningful when combined with other elements. Physical addresses are such an example. A street name by itself may not mean much without information about the city and postal code. It is not possible to indicate such a logical grouping with the VALUES clause.
With the SET clause, however, because it puts target columns and input sources next to each other, such a logical grouping can be expressed via an additional () around a group of columns. This is also part of the SQL standard for the UPDATE ... SET clause and adheres to the concept of self-documenting code, making it easier for the maintainer to understand that certain columns logically belong together. For INSERT INTO ... SET, this is another functionality that only Oracle Database currently supports:
Expand/Collapse
INSERT INTO employees
SET employee_id = 1,
job_id = 5,
(first_name, last_name) = ('Gerald', 'Venzl'),
(street, zip, city) = ('400 Oracle Parkway', 94065, 'Redwood Shores');
If a value for a column within a group is missed by accident, an ORA-00947: not enough values error is thrown. The statement below demonstrates that by omitting the value for the city column:
Expand/Collapse
SQL> INSERT INTO employees
SET employee_id = 1,
job_id = 5,
(first_name, last_name) = ('Gerald', 'Venzl'),
(street, zip, city) = ('400 Oracle Parkway', 94065);
Error at Command Line : 5 Column : 60
SQL Error: ORA-00947: not enough values
You may have noticed that this grouping syntax reintroduces positioned columns in the non-positional INSERT statement. So you probably don’t want to go overboard with it. Nevertheless, it is still a neat way to group logical entities.
In case you were thinking “what about this”:
Expand/Collapse
INSERT INTO employees
SET employee_id = 1,
job_id = 5,
(first_name = 'Gerald', last_name = 'Venzl'),
(street = '400 Oracle Parkway', zip = 94065, city = 'Redwood Shores');
Well, that doesn’t work, because the () already defines the beginning and end of a row. With this statement, you will get a syntax error because you are mixing the single-row and multi-row syntax.
Of course, one could wrap the group within the row, something like this (notice the nested ( () )):
Expand/Collapse
INSERT INTO employees
SET (employee_id = 1,
job_id = 5,
(first_name = 'Gerald', last_name = 'Venzl'),
(street = '400 Oracle Parkway', zip = 94065, city = 'Redwood Shores')
);
This syntax is currently not supported, but we are discussing it.
We rejected this syntax variation due to concerns about readability and maintainability. Poorly formatted SQL could easily obscure whether elements represent rows or column groups. However, one could argue that this construct maintains a consistent non-positional approach throughout the statement. Additionally, since another grouping construct exists, a case could be made for allowing this option and letting users choose which one to use. On the other hand, a counterargument can be made questioning the wisdom of offering a feature that risks enabling users to create problematic code.
It’s certainly food for thought. 🤔 If you have any good arguments for one of the other, leave me a comment with your thoughts. (Oracle employees, please just email me instead.)
What about INSERT INTO ... SELECT?
So far, I have only talked about INSERT INTO ... SET. You have probably already thought, “This is all great, but there is also INSERT INTO ... SELECT , dude”. Don’t you worry, we haven’t forgotten about that! 🙂 So let’s dive into the sub-select case and what we have done for that.
Before the non-positional INSERT statement, the columns returned by the SELECT were assigned to the target column via their position:
Expand/Collapse
SQL> INSERT INTO former_employees
SELECT employee_id, first_name, last_name, salary, job_id, SYSDATE
FROM employees
WHERE last_name = 'Forcher';
1 row inserted.
SQL> select * from former_employees;
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY JOB_CODE END_DATE
______________ _____________ ____________ _________ ___________ ___________
220 Sepp Forcher 2000 3 19-AUG-25
It’s all too easy to get the column positions mixed up – whoops, we mistakenly swapped first_name and last_name in the SELECT list below:
Expand/Collapse
SQL> INSERT INTO former_employees
SELECT employee_id, last_name, first_name, salary, job_id, SYSDATE
FROM employees
WHERE last_name = 'Prohaska';
1 row inserted.
SQL> select * from former_employees;
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY JOB_CODE END_DATE
______________ _____________ ____________ _________ ___________ ___________
220 Sepp Forcher 2000 3 19-AUG-25
230 Prohaska Herbert 5000 5 19-AUG-25
Wouldn’t it be convenient to map SELECT list columns to the target table using something other than their position? As it turns out, other SQL implementations have had the same idea and introduced the BY NAME option for the INSERT statement. This feature is now also available in Oracle Database. Actually, you have two options: you can explicitly use the BY POSITION keywords to maintain the traditional positional mapping, or you can use the BY NAME keywords to match source and target columns based on their names. These new keywords are placed directly before the SELECT keyword. If you do not specify them, the default behavior will remain BY POSITION, not to break backwards compatibility. The BY POSITION option is there for the SQL statement author to explicitly state the intended column mapping.
Here is the traditional behavior, but explicitly stated with BY POSITION, which mixes up first_name and last_name:
Expand/Collapse
SQL> INSERT INTO former_employees
BY POSITION
SELECT employee_id, last_name, first_name, salary, NULL, NULL
FROM employees
WHERE last_name = 'Forcher';
1 row inserted.
SQL> select * from former_employees;
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY JOB_CODE END_DATE
______________ _____________ ____________ _________ ___________ ___________
220 Forcher Sepp 2000
And once again executed with BY NAME instead:
Expand/Collapse
SQL> INSERT INTO former_employees
BY NAME
SELECT employee_id, last_name, first_name, salary
FROM employees
WHERE last_name = 'Forcher';
1 row inserted.
SQL> select * from former_employees;
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY JOB_CODE END_DATE
______________ _____________ ____________ _________ ___________ ___________
220 Sepp Forcher 2000
This time, we can see that the first and last names weren’t swapped, because the column position no longer matters. Notice something else interesting? There is no more NULL, NULL in the SELECT list either. Just like with the SET clause, matching columns by name removes the need to specify NULL for a non-existent value for a column. Instead, the column value can just be omitted.
Matching by column name is cool, but you will not always be so lucky that the source and target tables’ columns have identical names. I already alluded to that scenario above. If you look carefully, you will have seen that my employees table has a column job_id but my former_employees table calls it job_code. As one would expect, in a scenario where the names don’t match, the BY NAME functionality will throw an error:
Expand/Collapse
SQL> SELECT employee_id, job_id, last_name, first_name, salary
FROM employees
WHERE last_name = 'Forcher';
EMPLOYEE_ID JOB_ID LAST_NAME FIRST_NAME SALARY
______________ _________ ____________ _____________ _________
220 3 Forcher Sepp 2000
SQL> INSERT INTO former_employees
BY NAME
SELECT employee_id, job_id, last_name, first_name, salary
FROM employees
WHERE last_name = 'Forcher';
Error at Command Line : 1 Column : 1
SQL Error: ORA-00904: "JOB_ID": invalid identifier
Although it is bad data modeling, non-matching column names across tables are rather common. Even if it isn’t a different column name, what if the source is a result from a function? So is the BY NAME clause useless in practice, then? Or is there a way to match the column names regardless? The answer is: YES! Column names can also be matched via their column alias, meaning that all you have to do is alias the source column to the name of the target column. And even better, you just have to do that for the columns that don’t match, as demonstrated here via the AS keyword:
Expand/Collapse
SQL> INSERT INTO former_employees
BY NAME
SELECT employee_id,
job_id AS job_code,
last_name, first_name, salary
FROM employees
WHERE last_name = 'Forcher';
1 row inserted.
SQL> select * from former_employees;
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY JOB_CODE END_DATE
______________ _____________ ____________ _________ ___________ ___________
220 Sepp Forcher 2000 3
In reality, matching columns by alias will add quite a bit more value than it might seem from the example above. It is not uncommon for real queries to have rather lengthy expressions in their SELECT list. And for those cases, having to just add the AS <target column> will be rather handy:
Expand/Collapse
INSERT INTO employee_olap_results
BY NAME
-- complex OLAP query
SELECT
employee_id,
salary,
department_id,
AVG(salary) OVER (PARTITION BY department_id) AS dept_avg_salary,
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank_in_dept,
SUM(salary) OVER (PARTITION BY department_id ORDER BY employee_id) AS running_total_salary,
RATION_TO_REPORT(salary) OVER (PARTITION BY department_id) * 100 AS pct_of_dept_salary,
salary - AVG(salary) OVER (PARTITION BY department_id) AS salary_diff_from_dept_avg,
FIRST_VALUE(salary)
OVER (PARTITION BY department_id ORDER BY employee_id) AS first_salary_in_dept,
LAST_VALUE(salary)
OVER (PARTITION BY department_id
ORDER BY employee_id
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_salary_in_dept,
LEAD(salary, 1, salary)
OVER (PARTITION BY department_id ORDER BY salary) – salary AS salary_gap_to_next,
AVG(salary)
OVER (ORDER BY employee_id ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING) AS moving_avg_salary,
PERCENTILE_COUNT(0.5) WITHIN GROUP (ORDER BY salary) OVER () AS median_salary
FROM
employees
ORDER BY
department_id, employee_id;
Rigorous column list
Ok, we covered a lot so far, and we can clearly say, non-positional INSERT for the win, right? There are no downsides with this new syntax that we can think of, or are there? Actually, with everything described so far, there is one downside.
One benefit of the positional INSERT statement with an explicitly specified target column list for both the table value constructor (VALUES) and subquery sources is that the explicitly specified target column list provides the reader of the statement with a single, definite place to understand which columns are being targeted. The non-positional INSERT statement thus far sprinkles the column names all over the place, and the more columns that are involved, the less obvious the list of targeted columns becomes. Take the above OLAP example. In the traditional way of writing the statement, you would have to look in only one place to know the columns targeted:
Expand/Collapse
INSERT INTO employee_olap_results
(employee_id, salary, department_id, dept_avg_salary, salary_rank_in_dept, running_total_salary, pct_of_dept_salary,
salary_diff_from_dept_avg, first_salary_in_dept, last_salary_in_dept, salary_gap_to_next, moving_avg_salary, median_salary)
SELECT
employee_id,
salary,
department_id,
AVG(salary) OVER (PARTITION BY department_id),
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC),
SUM(salary) OVER (PARTITION BY department_id ORDER BY employee_id),
RATION_TO_REPORT(salary) OVER (PARTITION BY department_id) * 100,
salary - AVG(salary) OVER (PARTITION BY department_id),
FIRST_VALUE(salary)
OVER (PARTITION BY department_id ORDER BY employee_id),
LAST_VALUE(salary)
OVER (PARTITION BY department_id
ORDER BY employee_id
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),
LEAD(salary, 1, salary)
OVER (PARTITION BY department_id ORDER BY salary) – salary,
AVG(salary)
OVER (ORDER BY employee_id ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING),
PERCENTILE_COUNT(0.5) WITHIN GROUP (ORDER BY salary) OVER ()
FROM
employees
ORDER BY
department_id, employee_id;
The same applies to the traditional variant with VALUES:
Expand/Collapse
INSERT INTO parks
(park_id, park_code, name, full_name, url, description, designation, latitude, longitude, states, directions_info, directions_url, weather_info, country_id)
VALUES (
'77E0D7F0-1942-494A-ACE2-9004D2BDC59E',
'abli',
'Abraham Lincoln Birthplace',
'Abraham Lincoln Birthplace National Historical Park',
'https://www.nps.gov/abli/index.htm',
'For over a century people from around the world have come to rural Central Kentucky to honor the humble beginnings of our 16th president, Abraham Lincoln. His early life on Kentucky''s frontier shaped his character and prepared him to lead the nation through Civil War. Visit our country''s first memorial to Lincoln, built with donations from young and old, and the site of his childhood home.',
'National Historical Park',
37.5858662,
-85.67330523,
'KY',
'The Birthplace Unit of the park is located approximately 2 miles south of the town of Hodgenville on U.S. Highway 31E South. The Boyhood Home Unit at Knob Creek is located approximately 10 miles northeast of the Birthplace Unit of the park.',
'http://www.nps.gov/abli/planyourvisit/directions.htm', 'There are four distinct seasons in Central Kentucky. However, temperature and weather conditions can vary widely within those seasons. Spring and Fall are generally pleasant with frequent rain showers. Summer is usually hot and humid. Winter is moderately cold with mixed precipitation.',
'USA'
);
There is a benefit to that, to both the reader and the maintainer of the SQL statement. That’s why we decided that it should still be possible to define a column list for the non-positional INSERT statement. However, that column list now becomes a rigorous column list, meaning only the specified columns can be manipulated, and all listed columns must be included. Any extra or omitted columns in the SET clause or SELECT list will trigger an error. This enhances maintainability by making it easier to understand which columns are involved and protecting against unintended or missing columns. Neither the implementations providing BY NAME nor the ones providing SET allow for this facility, making it another feature that only Oracle Database supports today.
Here is an example with the SET clause specifying more columns than allowed by the rigorous column list:
Expand/Collapse
SQL> INSERT INTO employees (employee_id, first_name, last_name)
SET employee_id = 210,
first_name = 'Gerald',
last_name = 'Venzl',
salary = 1000,
job_id = 3;
Error at Command Line : 5 Column : 7
SQL Error: ORA-63850: A column specified in the INSERT INTO SET clause must be in the explicit column list.
And here is one example with BY NAME with fewer columns in the SELECT than listed in the rigorous column list:
Expand/Collapse
SQL> INSERT INTO former_employees
(employee_id, first_name, last_name, salary)
BY NAME
SELECT employee_id, first_name, salary
FROM employees
WHERE last_name = 'Forcher';
Error at Command Line : 4 Column : 21
SQL Error: ORA-63877: The explicit column list does not exactly match the exposed column names.
Naturally, the rigorous column list will reintroduce the requirement for row uniformity for multi-row SET clauses:
Expand/Collapse
INSERT INTO jobs
(job_id, job_title, min_salary)
SET (job_id = 3, job_title = 'Product Manager', min_salary = 5000),
(job_id = 4, job_title = 'Junior Consultant', min_salary = NULL),
(job_id = 5, job_title = 'Vice President', min_salary = NULL);
What’s next?
Standardization of non-positional INSERT
We believe the non-positional INSERT statement significantly enhances the SQL language. Therefore, Oracle plans to propose this comprehensive feature set to the SQL Standard Committee for inclusion in the next version of the SQL standard. I mentioned earlier that we already had discussions with the committee, but that was an understatement. I had the privilege of presenting the non-positional INSERT concepts to the committee at the June 2025 meeting, and based on the initial feedback, I am optimistic about a favorable outcome.
A plea to other SQL implementations
I strongly encourage all SQL implementations to add this feature to their roadmap. The Postgres community has already informally indicated that, once standardized, they will revisit their discussion on this topic. We have also informed the MySQL team. I hope that many more will follow suit. This feature shouldn’t be exclusive to Oracle Database. Every SQL user, regardless of the implementation, should benefit from it. SQL’s strength lies in its widely adopted, common syntax across implementations. Even if the immediate need for this feature isn’t apparent, supporting it strengthens the SQL language and benefits its users.
Closing comments
This blog post is quite extensive and took several hours to complete. I made every effort to avoid typos and ensure the accuracy of the SQL statements, but some errors may have slipped through. If you notice any typos, incorrect SQL, or find any part confusing, please leave a comment so I can make improvements.