The Complete Guide to the Oracle INSERT INTO Statement

The Oracle INSERT INTO statement is one of the most popular commands in Oracle, and it’s one of the first commands you learn to use. Read how to insert data and how to use the full functionality of the INSERT statement in this guide.

What Is the INSERT INTO Oracle Statement?

The Oracle INSERT INTO statement is used to insert (or add) data into a table.

The table needs to exist first. Creating a table is a separate step and is done as part of the CREATE statement (which I’ve written a guide about here).

This statement allows you to add data into a table in many ways and from several sources. There is also some Oracle-specific functionality, so if you’ve used INSERT in other databases, there’s some features here you may not have used before.

Then, we open the brackets, and inside the brackets, we have a list of columns. These are each of the columns that we want to insert values in to. We don’t need to specify all of the columns (I’ll explain this more shortly), but in this case I have.

Next comes the keyword VALUES.

Then, we open the brackets, and specify the values that we want to insert. Inside this list is a comma-separate list of values. These values align to the columns we specified earlier.

The first value matches the first column, the second value matches the second column, and so on.

The values can be specified as:

Numbers, which don’t need to be inside quotes.

Strings, which need to be inside single quotes

NULL, which means a NULL value will be inserted (which we have done with this example for the enrolment_date column)

You run a SELECT statement and the results of that are inserted into the table. The source_tablename is where the data comes from, and the target_tablename is where the data is inserted into.

There are a few tips for running an INSERT statement like this.

First of all, the columns and data types must match. You need to have the same number of columns, and you need to insert values of the same type. For example, you can’t insert a VARCAHR2 value into a NUMBER field.

Secondly, it’s a good idea to run the SELECT statement first as a test, by itself, before running the INSERT statement with the SELECT statement. This is so you can see the data that is returned by the SELECT statement and you can review it before it is inserted.

This query will insert new values into the student table. The values come from the new_students table.

For some columns, the names being inserted are the same as the names in the target table. For others, they are different.

In this example, we are inserting a value of 1000 for fees_required, for all records. We’re adding SYSDATE, which is today’s date, as the enrolment date for all students.

So, the column names don’t need to match, but the number of columns and their data type does.

Preventing Duplicate Records with INSERT If Not Exists

When you insert records into a database, sometimes you want to be sure that the record doesn’t already exist. You might not want to have duplicate records in your table.

How can you do this?

The good news is that it’s possible in Oracle SQL. The preferred method to do this is the MERGE statement, because it has been designed for this functionality.

However, if you really need to do it with an INSERT statement in Oracle, we can do it with a subquery.

Let’s say we have our student table again, and it has some records.

STUDENT_ID

FIRST_NAME

LAST_NAME

FEES_REQUIRED

FEES_PAID

ENROLMENT_DATE

GENDER

ADDRESS_STATE

1

John

Smith

500

100

01/Feb/15

M

New York

2

Susan

Johnson

150

150

12/Jan/15

F

Colorado

3

Tom

Capper

350

320

06/Mar/15

M

Nevada

4

Mark

Holloway

500

410

20/Jan/15

M

New York

5

Steven

Webber

100

80

09/Mar/15

M

New York

6

Julie

Armstrong

100

0

12/Feb/15

F

Texas

7

Michelle

Randall

250

23/Jan/15

F

Florida

8

Andrew

Cooper

800

400

04/Mar/15

M

Texas

9

Robert

Pickering

110

100

30/Jan/15

M

Colorado

10

Tanya

Hall

150

150

28/Jan/15

F

Texas

11

Jarrad

Winston

700

300

(null)

(null)

Utah

12

Mary

Taylor

500

100

(null)

F

Oregon

Now, we have our new_student table, which has some more records.

STUDENT_ID

FIRST_NAME

LAST_NAME

FEES_REQUIRED

FEES_PAID

ENROLMENT_DATE

GENDER

ADDRESS_STATE

1

Mark

Anderson

860

45

M

California

2

John

Rogers

210

700

M

Nevada

3

Susan

Johnson

500

0

F

Colorado

But, there are some duplicate records – Susan Johnson.

How do we define a duplicate record? That’s the important question here.

First, you’ll need to specify which columns define a duplicate record. In this example, let’s say that the columns to identify a duplicate record are the first_name and last_name columns (it doesn’t account for two students having the same name but it’s just for an example here).

We would need to write an INSERT statement that inserts our values, but use a WHERE clause to check that the value does not exist in the table already.

The data has been inserted. Even though the values clause specifies a fees_required value of 500 and the WHERE clause specifies it should be greater than 600, it doesn’t stop the value from being inserted.