FB

The Complete Guide to the Oracle INSERT INTO StatementThe 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.

 

Oracle INSERT INTO Syntax

The syntax of the INSERT statement in Oracle is:

INSERT INTO [ table_reference | (subquery) | TABLE (subquery2) ]
alias [ (column_names) ]
[ subquery3 | VALUES ( sql_expression ) [ returning_clause ] ]

This might seem pretty confusing now, especially with all of those options. Later in this guide, we’ll go through examples of each way of using the INSERT statement, which will help explain them.

 

The Basic INSERT Statement

Let’s start by looking at a basic INSERT statement.

Assume we have a table that looks like this:

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

We can use the INSERT statement to insert a new record into the table.

To use the INSERT statement in Oracle, we need a few things:

  • The name of the table
  • The values to insert into the table
  • The columns to insert the values into (this is actually optional)

We don’t need the names of the columns, but it’s good practice to specify them. If we don’t, then there’s a risk if the table structure changes, the INSERT statements will fail.

Let’s take a look at an INSERT statement for this table.

INSERT INTO student (student_id, first_name, last_name, fees_required, fees_paid, enrolment_date, address_state)
VALUES (11, 'Jarrad', 'Winston', 700, 300, NULL, 'Utah');

We start with the INSERT INTO, which are Oracle keywords.

Then, we mention the table. In this case, it’s student.

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)
  • Functions or other columns, which we will get to later.

Finally, we close the brackets and end with a semicolon.

What will this query do? Let’s run it and find out.

INSERT INTO student (student_id, first_name, last_name, fees_required, fees_paid, enrolment_date, address_state)
VALUES (11, 'Jarrad', 'Winston', 700, 300, NULL, 'Utah');

1 row inserted.

SELECT * FROM student;
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

As you can see, a single record has been added to this table.

 

INSERT Statement Without Columns

What happens if you have an Oracle INSERT INTO statement without specifying the columns?

Earlier I mentioned that they were optional, so what happens?

INSERT INTO student VALUES (12, 'Mary', 'Taylor', 500, 100, NULL, 'F', 'Oregon');

1 row inserted.

SELECT * FROM student;
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

We can see that the record was inserted. However, we could have some problems with this:

  • What if we alter the table and add or remove columns? The INSERT statement may not work anymore.
  • What if we put the values in the wrong order in the INSERT statement? It will also fail.

So, while it could work, it’s generally not a good idea.

Get Your Free PDF: 9 Ways to Improve your Database Skills

Insert All in Oracle – Inserting Multiple Records With One Statement

What if you had a few records you wanted to insert?

You could run several different INSERT statements. But, there’s another way to do it.

You can insert several records at once, with a single statement, using the INSERT ALL keyword.

Why would you want to do this? Generally, running a single statement is better for performance than many statements. It can also be easier to write, especially if there are many records to insert.

So, how do you do this?

Let’s see an example.

INSERT ALL
INTO student VALUES (11, 'Jarrad', 'Winston', 700, 300, NULL, 'Utah')
INTO student VALUES (12, 'Michael', 'Brown', 750, 500, NULL, 'Oregon')
INTO student VALUES (13, 'Paul', 'Masters', 200, 10, NULL, 'Texas')
SELECT * FROM dual;

This will insert three records in a single statement. You can, of course, have many more than three records.

You need to have the SELECT * FROM dual at the end, because the INSERT ALL expects a SELECT statement., and this will allow you to insert many values manually in one statement.

 

Inserting Data From a Query

Let’s say you wanted to create a new table, and populate it. But, you want to populate the data with the results of another query.

What could you do?

You could try:

  • Run the SELECT query
  • Copy the output to a spreadsheet file
  • Use formulas to write a series of INSERT statements
  • Copy those INSERT statements to an SQL file
  • Run the SQL file

Sounds like a lot of work, right?

There is an easier way.

You can use an Oracle INSERT INTO statement based on the results of a SELECT query – all in the one statement.

The syntax for this is:

INSERT INTO target_tablename (col1, col2, col3...)
SELECT (col1, col2, col3...)
FROM source_tablename...

You run a SELECT statament 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.

You can read my guide on this technique here.

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.

Let’s see an example.

INSERT INTO student (student_id, first_name, last_name, fees_required, fees_paid, enrolment_date, home_state)
SELECT student_id, first_name, last_name, 1000, 0, SYSDATE, home_state
FROM new_students
WHERE requested_date > SYSDATE - 30;

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.

Our query could look like this:

INSERT INTO student (student_id, first_name, last_name, fees_required, fees_paid, enrolment_date, gender, address_state)
SELECT student_id, first_name, last_name, fees_required, fees_paid, enrolment_date, gender, address_state
FROM new_student
WHERE NOT EXISTS (
  SELECT *
  FROM student
  WHERE student.first_name = new_student.first_name
  AND student.last_name = new_student.last_name
);

2 rows inserted.

This query will insert the values from the new_student table into the student table, where the first_name and last_name records to not match.

Let’s see what this table now shows.

SELECT * FROM student;
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
100 Mark Anderson 860 45 (null) M California
102 John Rogers 210 700 (null) M Nevada

You can see the student table has been updated.

As I mentioned before, the best way to do this is using a MERGE statement, but if you need to insert values if they don’t exist using INSERT, then this is how you can do it.

 

INSERT Date Values Into a Table

I’ve often had trouble inserting DATE values into an Oracle table. Most of the time it’s because I don’t know the format that is needed, or I get confused between Oracle and other databases.

Inserting a date is pretty simple in Oracle.

You can do it in one of two ways:

  • Specify the date in a string that matches your databases default format
  • Use the TO_DATE function and specify the format of your date

Let’s take a look at both, using a sample table:

CREATE TABLE datetest (
ID NUMBER,
date_test DATE); 

First, we can insert a date value by specifying a date using the default date format.

How do we know what the default date format is?

We can check the NLS_DATABASE_PARAMETERS table.

SELECT value
FROM SYS.NLS_DATABASE_PARAMETERS
WHERE parameter = 'NLS_DATE_FORMAT';
VALUE
DD-MON-RR

On my database, the format is set to DD-MON-RR. This means I need to specify the day, then the month in three letters, then the year in four digits.

Let’s try an insert statement.

INSERT INTO datetest(id, date_test) VALUES (1, '20-JAN-2016');

1 row inserted.

This row was inserted successfully.

How about using the format DD-MM-YY?

INSERT INTO datetest (id, date_test) VALUES (2, '20-01-2016');

SQL Error: ORA-01843: not a valid month

So, if you want to specify the date value in a string with no other functions, it needs to match the format of your database.

The second way is to use the TO_DATE function. This is good because you can actually specify the format of the date you’re inserting.

Let’s use this function.

INSERT INTO datetest (id, date_test) VALUES (3, TO_DATE('20-01-2016', 'DD-MM-YYYY'));

1 row inserted.

This value has been inserted correctly because I specified the format of the date used in the TO_DATE function. (DD-MM-YYYY) This can be different to your database format.

You can specify a range of formats:

INSERT INTO datetest (id, date_test) VALUES (4, TO_DATE('JAN 24 2016', 'MON DD YYYY'));
INSERT INTO datetest (id, date_test) VALUES (5, TO_DATE('01-25-2016', 'MM-DD-YYYY'));
INSERT INTO datetest (id, date_test) VALUES (6, TO_DATE('26/01/16', 'DD/MM/YY'));
INSERT INTO datetest (id, date_test) VALUES (7, TO_DATE('01/27/2016', 'MM/DD/YYYY')); 

All of these rows will be inserted.

Let’s take a look at the table now.

SELECT * FROM datetest;
ID DATE_TEST
1 20/Jan/16
3 23/Jan/16
4 24/Jan/16
5 25/Jan/16
6 26/Jan/16
7 27/Jan/16

You can see that all of the dates have been inserted here. Also, it’s showing the values in the DD/MMM/YY format because that’s my default database format.

 

Multi-Table Insert Based On Conditions with INSERT FIRST WHEN and INSERT ALL WHEN

Sometimes, you might want to insert into different tables depending on certain conditions.

Perhaps you want to insert the most recent month of data into one table and the rest of the data into another table.

Or, perhaps you want to insert data from different sources into different tables.

Whatever reason you have, you can do this using the INSERT WHEN syntax.

What does this INSERT WHEN syntax look like?

INSERT [FIRST|ALL]
WHEN (condition) THEN
INTO table_name (columns)
VALUES (values)
WHEN (condition) THEN
INTO table_name (columns)
VALUES (values)
ELSE
INTO table_name (columns)
VALUES (values)
SELECT (columns)
FROM table_name

It looks quite long, but it gets the job done. It looks kind of like a long IF statement, with some conditions and an ELSE clause.

Now, you can specify either INSERT FIRST or INSERT ALL. What’s the difference between INSERT FIRST, and INSERT ALL?

In short, the INSERT FIRST will stop looking for conditions to evaluate once it has found the first condition that matches, where as the INSERT ALL will keep evaluating conditions.

INSERT FIRST will only ever insert one record at the most, where as INSERT ALL may insert records into all tables.

Let’s see some examples of both of these methods.

 

Inserting Into Multiple Tables with INSERT ALL WHEN

Let’s see an example of this syntax, using our student table. We’ll create a few new tables and insert the data into different tables based on the month that the student enrolled.

This is another handy piece of functionality of the INSERT statement in Oracle.

Here is the student table.

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 Utah
12 Mary Taylor 500 100 F Oregon
100 Mark Anderson 860 45 M California
102 John Rogers 210 700 M Nevada

Now, let’s create a few new tables to store records for each month. It may not be a realistic example, but it demonstrates the concept well.

CREATE TABLE jan_enrolments (
student_id NUMBER,
enrolment_date DATE);

CREATE TABLE feb_enrolments (
student_id NUMBER,
enrolment_date DATE);

CREATE TABLE mar_enrolments (
student_id NUMBER,
enrolment_date DATE);

CREATE TABLE other_enrolments (
student_id NUMBER,
enrolment_date DATE);

These four tables have been created. The other_enrolments table is for students that don’t fit into the other three tables. This will make more sense soon.

Now, let’s use our INSERT ALL WHEN statement to insert values into these tables.

Our INSERT ALL WHEN statement would look like this:

INSERT ALL
WHEN enrolment_date >= '01-MAR-2015' THEN
  INTO mar_enrolments (student_id, enrolment_date)
  VALUES (student_id, enrolment_date)
WHEN enrolment_date >= '01-FEB-2015' THEN
  INTO feb_enrolments (student_id, enrolment_date)
  VALUES (student_id, enrolment_date)
WHEN enrolment_date >= '01-JAN-2015' THEN
  INTO jan_enrolments (student_id, enrolment_date)
  VALUES (student_id, enrolment_date)
ELSE
  INTO other_enrolments (student_id, enrolment_date)
  VALUES (student_id, enrolment_date)
SELECT student_id, enrolment_date
FROM student;

The logic in this statement says that:

  • If the enrolment date is after March 1st, add the record into the mar_enrolments table.
  • Then, if the enrolment date is after Feb 1st, add the record into the feb_enrolments table.
  • Then, if the enrolment date is after Jan 1st, add the record into the jan_enrolments table.
  • If it doesn’t meet any of these criteria, add the record into the other_enrolments table.

In this statement, if a record has an enrolment date of 10-MAR-2016, then it will satisfy the conditions for all three WHEN statements, meaning it will be inserted into all three tables.

Let’s see the results.

SELECT student_id, enrolment_date
FROM jan_enrolments;
STUDENT_ID ENROLMENT_DATE
1 01/Feb/15
2 12/Jan/15
3 06/Mar/15
4 20/Jan/15
5 09/Mar/15
6 12/Feb/15
7 23/Jan/15
8 4/Mar/15
9 30/Jan/15
10 28/Jan/15

 

SELECT student_id, enrolment_date
FROM feb_enrolments;
STUDENT_ID ENROLMENT_DATE
1 01/Feb/15
3 6/Mar/15
5 09/Mar/15
6 12/Feb/15
8 04/Mar/15

 

SELECT student_id, enrolment_date
FROM mar_enrolments;
STUDENT_ID ENROLMENT_DATE
3 06/Mar/15
5 9/Mar/15
8 04/Mar/15

 

SELECT student_id, enrolment_date
FROM other_enrolments;
STUDENT_ID ENROLMENT_DATE
11 (null)
12 (null)
100 (null)
102 (null)

As you can see, there are some records that have been inserted into multiple tables.

 

Inserting Into Multiple Tables with INSERT FIRST WHEN

What if you only wanted to insert the source record once into one table?

This is where you would use the INSERT FIRST WHEN syntax.

This syntax means that the conditions will be checked for each row, and when one condition is true, the record is inserted but the statement moves to the next record.

Let’s use the same example as before, but use the INSERT FIRST WHEN syntax.

INSERT FIRST
WHEN enrolment_date >= '01-MAR-2015' THEN
  INTO mar_enrolments (student_id, enrolment_date)
  VALUES (student_id, enrolment_date)
WHEN enrolment_date >= '01-FEB-2015' THEN
  INTO feb_enrolments (student_id, enrolment_date)
  VALUES (student_id, enrolment_date)
WHEN enrolment_date >= '01-JAN-2015' THEN
  INTO jan_enrolments (student_id, enrolment_date)
  VALUES (student_id, enrolment_date)
ELSE
  INTO other_enrolments (student_id, enrolment_date)
  VALUES (student_id, enrolment_date)
SELECT student_id, enrolment_date
FROM student;

The logic in this statement says that:

  • If the enrolment date is after March 1st, add the record into the mar_enrolments table.
  • If that is not true, and if the enrolment date is after Feb 1st, add the record into the feb_enrolments table.
  • If that is not true, and if the enrolment date is after Jan 1st, add the record into the jan_enrolments table.
  • If that is not true, add the record into the other_enrolments table.

This ensures that the source record is only inserted into one of these tables.

Let’s take a look at the results.

SELECT student_id, enrolment_date
FROM jan_enrolments;
STUDENT_ID ENROLMENT_DATE
2 12/Jan/15
4 20/Jan/15
7 23/Jan/15
9 30/Jan/15
10 28/Jan/15

 

SELECT student_id, enrolment_date
FROM feb_enrolments;
STUDENT_ID ENROLMENT_DATE
1 1/Feb/15
6 12/Feb/15

 

SELECT student_id, enrolment_date
FROM mar_enrolments;
STUDENT_ID ENROLMENT_DATE
3 6/Mar/15
5 9/Mar/15
8 4/Mar/15

 

SELECT student_id, enrolment_date
FROM other_enrolments;
STUDENT_ID ENROLMENT_DATE
11 (null)
12 (null)
100 (null)
102 (null)

As you can see, there are no duplicated records in the new enrolment tables. Each record has only ended up in one table.

 

Restrictions for Multi-Table Inserts

Whether you use the INSERT ALL, INSERT ALL WHEN or INSERT FIRST WHEN, there are a few restrictions:

  • You cannot insert into views (regular or materialized) or remote tables.
  • The total number of columns used in all INTO clauses cannot exceed 999.
  • You cannot use a TABLE collection expression.
  • It’s not recommended to use sequences in multi-table inserts.

 

Validating the INSERT Data with the WITH CHECK OPTION

When you insert data into a table, you may want to restrict data that gets inserted based on a WHERE condition.

When you run an UPDATE statement on a view, you can add a WITH CHECK OPTION clause to make sure that your updates adhere to the WHERE clause of the view.

You can do the same thing with an INSERT statement.

The syntax for this is:

INSERT INTO (
sql_statement WITH CHECK OPTION)
VALUES (values);

The sql_statement is a SELECT statement that has a WHERE clause. You can use this to insert data into.

Let’s use our student table for this example again.

Here is our student table.

SELECT student_id, first_name, last_name, fees_required
FROM student;
STUDENT_ID FIRST_NAME LAST_NAME FEES_REQUIRED
1 John Smith 500
2 Susan Johnson 150
3 Tom Capper 350
4 Mark Holloway 500
5 Steven Webber 100
6 Julie Armstrong 100
7 Michelle Randall 250
8 Andrew Cooper 800
9 Robert Pickering 110
10 Tanya Hall 150
11 Jarrad Winston 700
12 Mary Taylor 500
100 Mark Anderson 860
102 John Rogers 210

Now, let’s insert into this table, but using the SELECT statement:

INSERT INTO (
SELECT student_id, first_name, last_name, fees_required
FROM student
)
VALUES (120, 'A', 'A', 500);

1 row inserted.

Let’s see the results.

SELECT student_id, first_name, last_name, fees_required
FROM student;
STUDENT_ID FIRST_NAME LAST_NAME FEES_REQUIRED
1 John Smith 500
2 Susan Johnson 150
3 Tom Capper 350
4 Mark Holloway 500
5 Steven Webber 100
6 Julie Armstrong 100
7 Michelle Randall 250
8 Andrew Cooper 800
9 Robert Pickering 110
10 Tanya Hall 150
11 Jarrad Winston 700
12 Mary Taylor 500
100 Mark Anderson 860
102 John Rogers 210
120 A A 500

The value has been inserted into the table, with NULL values where we didn’t specify column values.

Now, let’s try it with a WHERE clause.

INSERT INTO (
SELECT student_id, first_name, last_name, fees_required
FROM student
WHERE fees_required > 600
)
VALUES (121, 'B', 'B', 500);

1 row inserted.

Let’s see the results.

SELECT student_id, first_name, last_name, fees_required
FROM student;
STUDENT_ID FIRST_NAME LAST_NAME FEES_REQUIRED
1 John Smith 500
2 Susan Johnson 150
3 Tom Capper 350
4 Mark Holloway 500
5 Steven Webber 100
6 Julie Armstrong 100
7 Michelle Randall 250
8 Andrew Cooper 800
9 Robert Pickering 110
10 Tanya Hall 150
11 Jarrad Winston 700
12 Mary Taylor 500
100 Mark Anderson 860
102 John Rogers 210
120 A A 500
121 B B 500

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.

Let’s try it again, but we’ll use the WITH CHECK OPTION keywords.

INSERT INTO (
SELECT student_id, first_name, last_name, fees_required
FROM student
WHERE fees_required > 600 WITH CHECK OPTION
)
VALUES (122, 'C', 'C', 500);

SQL Error: ORA-01402: view WITH CHECK OPTION where-clause violation

Let’s see the results.

SELECT student_id, first_name, last_name, fees_required
FROM student;
STUDENT_ID FIRST_NAME LAST_NAME FEES_REQUIRED
1 John Smith 500
2 Susan Johnson 150
3 Tom Capper 350
4 Mark Holloway 500
5 Steven Webber 100
6 Julie Armstrong 100
7 Michelle Randall 250
8 Andrew Cooper 800
9 Robert Pickering 110
10 Tanya Hall 150
11 Jarrad Winston 700
12 Mary Taylor 500
100 Mark Anderson 860
102 John Rogers 210
120 A A 500
121 B B 500

This time, the record is not inserted. This is because the WITH CHECK OPTION causes the INSERT statement to not insert the record if the record does not comply with the WHERE clause.

This is a helpful way to validate your data before inserting into your table.

 

Conclusion to the Complete Guide to the Oracle INSERT INTO Statement

That brings us to the end of this article. In conclusion, the INSERT statement is quite powerful and allows you to do many things:

  • Insert data into a table
  • Insert data without specifying columns
  • Inserting all records into multiple tables
  • Inserting some records into one or more table
  • Preventing duplicate values from being inserted
  • Validating your data using the WITH CHECK OPTION

Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!

Get Your FREE PDF: 9 Ways to Improve your Database Skills