Oracle Insert
Examples and syntax explanation
before implementing them on a production server.
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
No column list
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
Using a column list
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.
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.
