Oracle SQL INSERT Statement

Overview

We can use Oracle SQL INSERT Statement to add new rows into an Oracle database table. INSERT statement can be used to add data to a single table or multiple tables.

INSERT is a DML statement( Data Manipulation Language ). DML statements are SQL statements that modify the data in the Oracle database.

In previous post, we have created dept and emp tables. We will insert sample data into these tables.

https://www.testingdocs.com/oracle-sql-create-table-statement/

Example – INSERT single row

We will insert a single row to dept table. The dept table has three columns(deptno,dname,loc)

SQL query to insert data into all the three columns.

INSERT INTO dept VALUES
(1000,’ACCOUNTING’,’NEW YORK’);

Oracle SQL Insert Statement

We will get the SQL success message : 1 row created.

Common Errors

Example – Unique constraint violated

SQL> INSERT INTO dept VALUES (2000,’RESEARCH’,’DALLAS’);
INSERT INTO DEPT VALUES (2000,’RESEARCH’,’DALLAS’)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.PK_DEPT) violated

We know that deptno column is the primary key for the dept table. The values for the deptno column should be unique in the table. If we try to insert duplicate values for the deptno column values, we would get an error. This is because the unique constraint on the primary key column for the table is violated. There is already a row with deptno with 2000 present in the dept table.

The following error would be displayed:

ERROR at line 1:
ORA-00001: unique constraint (SCOTT.PK_DEPT) violated

SCOTT is the Oracle schema, PK_DEPT is the primary key constraint name for the table. In Oracle, to uniquely identify the database object names we use the dot notation.

<database_schema>.<database object>