Oracle Insert
Examples and explanation
The Oracle Insert statement allows you to add data to a table.
Contents :
- Inserting without specifying column names
- Inserting by specifying column names
- Insert a column’s default value
- Inserting a null value
- Inserting multiple rows by means of a subquery
The syntax is : insert into <table_name> ( <column list>) values (<values list>);
You do not have to specify the column names if your value list is in the same order as the columns of the table, but it is best to always specify the column names.
Sample table for use in the Oracle Insert syntax tutorial:
SQL> select * from dept order by deptno;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
When you do not specify the column list, you can use the values clause immediately after
the table clause, but Oracle will expect the values to match the column datatypes,
so you must make sure that the values are in the same order as the table’s columns.
To see what the order of a table’s columns is,
use the ‘DESCRIBE’ statement or its abbreviation ‘desc’ :
SQL> describe dept Name Null? Type ---------------------- -------- --------------- DEPTNO NUMBER(2) DNAME VARCHAR2(14) LOC VARCHAR2(13)
SQL> insert into dept values
(50,'MARKETING','MELBOURNE');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from dept order by deptno;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 MARKETING MELBOURNE
SQL> insert into dept (deptno,dname,loc)
values (60,'IT','HARTFORD');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from dept order by deptno;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 MARKETING MELBOURNE
60 IT HARTFORD
6 rows selected.
Using a column list allows you to insert values for selected columns only:
SQL> insert into dept (deptno,dname)
values (70,'CALLCENTER');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from dept order by deptno;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 MARKETING MELBOURNE
60 IT HARTORD
70 CALLCENTER
7 rows selected.
Default values
You can choose to insert a column’s default value like this:
Let’s first change the DEPT table to have a default value of ‘NONE’ for the location column:
SQL> alter table dept modify (loc default 'NONE');
Table altered.
SQL> insert into dept (deptno,dname,loc)
values (80,'SUPPORT',DEFAULT);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from dept order by deptno;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 MARKETING MELBOURNE
60 IT HARTORD
70 CALLCENTER
80 SUPPORT NONE
8 rows selected.
Use the ‘null’ identifier to insert a null value instead of a real value.
SQL> insert into dept (deptno,dname,loc)
values (90,NULL,'BOISE');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from dept order by deptno;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 MARKETING MELBOURNE
60 IT HARTORD
70 CALLCENTER
80 SUPPORT NONE
90 BOISE
9 rows selected.
Inserting multiple rows by means of a subquery
Instead of adding one row at a time, here is an example of inserting a number of rows by using a subquery:
We have another table called DEPT_MERGE with the same table structure as DEPT and with rows which need to be added to DEPT.
SQL> select * from dept_merge order by deptno;
DEPTNO DNAME LOC
---------- -------------- -------------
91 SHIPPING DETROIT
92 FINANCE DETROIT
SQL> insert into dept
2 (select * from dept_merge);
2 rows created.
SQL> commit;
Commit complete.
SQL> select * from dept order by deptno;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 MARKETING MELBOURNE
60 IT HARTORD
70 CALLCENTER
80 SUPPORT NONE
90 BOISE
91 SHIPPING DETROIT
92 FINANCE DETROIT
11 rows selected.
Lastly: do not forget to commit to make your changes permanent after you use the Oracle Insert statement.
Recommended reading:
Posted: October 23rd, 2009 under SQL.
Tags: insert syntax, oracle insert, sql insert