DBA Hub

📋Steps in this guide1/4

Non-Positional INSERT INTO SET and INSERT INTO BY NAME Clauses in Oracle Database 23ai/26ai

Oracle database 23ai/26ai gives us variations on the traditional INSERT INTO VALUES and INSERT INTO SELECT syntax. The new variations make code simpler and more readable.

oracle 23configurationintermediate
by OracleDba
16 views
1

Setup

We need the following objects to run our tests. We create a test table called . We create a table called as a copy of , but without the data.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
drop table if exists emp purge;

create table emp (
  empno    number(4,0), 
  ename    varchar2(10 byte), 
  job      varchar2(9 byte), 
  mgr      number(4,0), 
  hiredate date, 
  sal      number(7,2), 
  comm     number(7,2), 
  deptno   number(2,0), 
  constraint pk_emp primary key (empno)
);
  
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values
  (7369,'SMITH','CLERK',7902,to_date('17-DEC-80','DD-MON-RR'),800,null,20),
  (7499,'ALLEN','SALESMAN',7698,to_date('20-FEB-81','DD-MON-RR'),1600,300,30),
  (7521,'WARD','SALESMAN',7698,to_date('22-FEB-81','DD-MON-RR'),1250,500,30),
  (7566,'JONES','MANAGER',7839,to_date('02-APR-81','DD-MON-RR'),2975,null,20),
  (7654,'MARTIN','SALESMAN',7698,to_date('28-SEP-81','DD-MON-RR'),1250,1400,30),
  (7698,'BLAKE','MANAGER',7839,to_date('01-MAY-81','DD-MON-RR'),2850,null,30),
  (7782,'CLARK','MANAGER',7839,to_date('09-JUN-81','DD-MON-RR'),2450,null,10),
  (7788,'SCOTT','ANALYST',7566,to_date('19-APR-87','DD-MON-RR'),3000,null,20),
  (7839,'KING','PRESIDENT',null,to_date('17-NOV-81','DD-MON-RR'),5000,null,10),
  (7844,'TURNER','SALESMAN',7698,to_date('08-SEP-81','DD-MON-RR'),1500,0,30),
  (7876,'ADAMS','CLERK',7788,to_date('23-MAY-87','DD-MON-RR'),1100,null,20),
  (7900,'JAMES','CLERK',7698,to_date('03-DEC-81','DD-MON-RR'),950,null,30),
  (7902,'FORD','ANALYST',7566,to_date('03-DEC-81','DD-MON-RR'),3000,null,20),
  (7934,'MILLER','CLERK',7782,to_date('23-JAN-82','DD-MON-RR'),1300,null,10);
commit;

create table emp2 as
select *
from   emp
where  1 = 2;
2

INSERT INTO SET Clause

A typical insert into a table may look something like this. The list of columns is optional provided we include a value for all columns in the correct order. We can insert a subset of columns provided we include all mandatory columns, and the column list and values are consistent. When we look at the list it's not instantly apparent which values relate to which columns. This can be even more problematic for tables with large number of columns. Oracle database 23ai/26ai (23.9) introduces the clause, making statements similar to statements, which can be much easier to read. The following example shows an insert of all columns into the EMP2 table using the INSERT INTO SET clause. Not surprisingly, if we want to insert a subset of columns, we just remove them from the clause. The following example uses to process multiple rows in a single statement. For a single row the parentheses are optional, but when processing multiple rows each row must be surrounded by parenthesis. Notice that each row can insert into different columns. This looks similar to the way we use the table values constructor for multiple inserts in a single statement, but is more flexible.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
-- All columns.
insert into emp2 (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (9998, 'HALL', 'DBA', 6798, to_date('06-AUG-2025','DD-MON-YYYY'), 1000, null, 20);

-- Subset of columns.
insert into emp2 (empno, ename, job)
values (9999, 'HALL', 'DBA');

rollback;

insert into emp2 set
  empno    = 9998,
  ename    = 'HALL',
  job      = 'DBA',
  mgr      = 6798,
  hiredate = to_date('06-AUG-2025','DD-MON-YYYY'),
  sal      = 1000,
  comm     = null,
  deptno   = 20;

1 row created.

SQL>

insert into emp2 set
  empno    = 9999,
  ename    = 'HALL',
  job      = 'DBA';

1 row created.

SQL>

rollback;

insert into emp2 set
  (empno = 9997, ename = 'HALL', job = 'DBA', deptno = 20),
  (empno = 9998, ename = 'SMITH', job = 'DBA', sal = 1000),
  (empno = 9999, ename = 'JONES', job = 'DBA', hiredate = to_date('06-AUG-2025','DD-MON-YYYY'));

3 rows created.

SQL>

rollback;
3

INSERT INTO BY NAME Clause

Typical statements look something like this. The list of columns is optional provided we include a value for all columns in the correct order. In our first example we can ignore the column lists as the two tables have the same structure. In the second example we explicitly listed all the columns. In the last example we inserted a subset of columns. We have to make sure the column list and select list are in the correct order, which can become tricky for more complex queries. Oracle database 23ai/26ai (23.9) introduces the clause, which means columns are matched using their name or alias if an alias is used. This way we don't have to worry about explicitly listing columns, and the order of the columns is no longer important. The following examples show inserts of all columns into the EMP2 table using the clause. The column order of the tables can change and the inserts are not affected because the matching is based on their names/aliases. We'll come back to the all columns implicit example later. If we want to insert a subset of columns, we just reduce the number of columns in the select list. As long as the column names, or aliases, and data types remain consistent with those in the destination table, we can modify the columns in the query. In this example we amend the salary as part of the transfer. If the destination table is a superset of the source table, we can still perform a basic "all columns implicit" insert safely.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
-- All columns implicit.
insert into emp2
select *
from   emp;

14 rows created.

SQL>

rollback;


-- All columns explicit.
insert into emp2 (empno, ename, job ,mgr, hiredate, sal, comm, deptno)
select empno,
       ename,
       job,
       mgr,
       hiredate,
       sal,
       comm,
       deptno
from emp;

14 rows created.

SQL>

rollback;


-- Subset of columns.
insert into emp2 (empno, ename, job)
select empno,
       ename,
       job
from   emp;

14 rows created.

SQL>

rollback;

-- All columns implicit.
insert into emp2 by name
select *
from   emp;

14 rows created.

SQL>

rollback;


-- All columns explicit.
insert into emp2 by name
select empno,
       ename,
       job,
       mgr,
       hiredate,
       sal,
       comm,
       deptno
from   emp;

14 rows created.

SQL>

rollback;


-- All columns explicit. Order of columns changed.
insert into emp2 by name
select deptno,
       sal,
       comm,
       empno,
       ename,
       job,
       mgr,
       hiredate
from   emp;

14 rows created.

SQL>

rollback;

-- Subset of columns.
insert into emp2 by name
select empno,
       ename,
       job
from   emp;

14 rows created.

SQL>

rollback;

insert into emp2 by name
select empno,
       ename,
       job,
       sal*2 as sal
from   emp;

14 rows created.

SQL>

rollback;

-- Add extra column to destination.
alter table emp2 add (extra_column VARCHAR2(10));


-- Try an old-style insert select. It will fail.
insert into emp2
select * from emp;
            *
ERROR at line 1:
ORA-00947: not enough values
Help: https://docs.oracle.com/error-help/db/ora-00947/

SQL>


-- Repeat using BY NAME. It works.
insert into emp2 by name
select * from emp;

14 rows created.

SQL>

rollback;
4

INSERT INTO BY POSITION Clause

The clause is an explicit version of the classic positional approach. All columns have to present and in the correct order for the insert to work. If we want to omit optional columns, they have to be replaced by a null. For more information see: - INSERT - Table Values Constructor in Oracle Database 23ai/26ai Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
insert into emp2 by position
select empno,
       ename,
       job,
       mgr,
       hiredate,
       sal,
       comm,
       deptno
from   emp;

insert into emp2 by position
select empno,
       ename,
       job,
       mgr,
       null,
       null,
       null,
       null
from   emp;

Comments (0)

Please to add comments

No comments yet. Be the first to comment!