SQL INSERT: The Complete Guide

The SQL INSERT INTO statement is one of the most popular commands in SQL, 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 SQL INSERT Statement?

The INSERT statement, or 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 vendor-specific functionality, so if you've used INSERT in other databases, there are some features here you may not have used before.

SQL INSERT INTO Syntax

The INSERT statement has a lot of settings and variations, but there's also a pretty basic way to use it and insert data.

The syntax of the basic INSERT statement is common to all database vendors:

1INSERT INTO table_reference [ (column_names) ]
2[ 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:

Here's the Create Table statement for this table (which is also available on my GitHub repository here).

1CREATE TABLE student (
2    student_id INT,
3    first_name VARCHAR(50),
4    last_name VARCHAR(50),
5    fees_required INT,
6    fees_paid INT,
7    enrolment_date DATE,
8    gender VARCHAR(1)
9);

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

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

• The name of the table we want to insert data into
• 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 that if the table structure changes, the INSERT statements will fail.

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

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

We start with the INSERT INTO, which are SQL 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-separated list of values. These values align with 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.

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

11 row inserted.

1SELECT * FROM student;

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

INSERT Statement Without Columns

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

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

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

11 row inserted.

1SELECT *
2FROM student;

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.

Insert Multiple Rows in One Statement

Our examples so far have shown the ability to insert a single value with a statement. What if you wanted to insert multiple values?

You could write separate statements:

1INSERT INTO student
2(student_id, first_name, last_name, fees_required, fees_paid, enrolment_date)
3VALUES (11, 'Jarrad', 'Winston', 700, 300, NULL);
4INSERT INTO student
5(student_id, first_name, last_name, fees_required, fees_paid, enrolment_date)
6VALUES (12, 'Michael', 'Brown', 750, 500, NULL);
7INSERT INTO student
8(student_id, first_name, last_name, fees_required, fees_paid, enrolment_date)
9VALUES (13, 'Paul', 'Masters', 200, 10, NULL);

This can get a bit "wordy" and repetitive. Also, if you're inserting hundreds or thousands of rows, it can take a bit of time as each statement is processed individually.

Fortunately, there is a better way.

You can insert multiple records with a single SQL INSERT statement.

The way to do this is different with each database vendor. Let's take a look.

Oracle: Insert Multiple Records

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.

1INSERT ALL
2INTO student (student_id, first_name, last_name, fees_required, fees_paid, enrolment_date)
3VALUES (11, 'Jarrad', 'Winston', 700, 300, NULL)
4INTO student (student_id, first_name, last_name, fees_required, fees_paid, enrolment_date)
5VALUES (12, 'Michael', 'Brown', 750, 500, NULL)
6INTO student (student_id, first_name, last_name, fees_required, fees_paid, enrolment_date)
7VALUES (13, 'Paul', 'Masters', 200, 10, NULL)
8SELECT * 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 using the DUAL dummy table will allow you to insert many values manually in one statement.

SQL Server Insert Multiple Rows

Inserting multiple records in a single statement is easier in SQL Server as it requires fewer words. It's the same as MySQL and PostgreSQL.

You can separate each row with a comma outside the brackets.

For example:

1INSERT INTO student (student_id, first_name, last_name, fees_required, fees_paid, enrolment_date)
2VALUES
3(11, 'Jarrad', 'Winston', 700, 300, NULL),
4(12, 'Michael', 'Brown', 750, 500, NULL),
5(13, 'Paul', 'Masters', 200, 10, NULL);

There are a few things to notice here.

The column names are only specified once, after the table name, which is the same as if you were inserting a single record.

Several rows are mentioned after the VALUES keyword, and they are separated by a comma.

Running this statement will cause all three rows to be inserted into the table at once.

Also, you can only INSERT up to 1,000 rows in a single statement. Any more than that and you can either run a second statement or change your approach to loading data.

MySQL Insert Multiple Rows

The way to insert multiple rows is the same as SQL Server and PostgreSQL, where you specify the column names once and separate each row in the VALUES clause with a comma.

For example:

1INSERT INTO student (student_id, first_name, last_name, fees_required, fees_paid, enrolment_date)
2VALUES
3(11, 'Jarrad', 'Winston', 700, 300, NULL),
4(12, 'Michael', 'Brown', 750, 500, NULL),
5(13, 'Paul', 'Masters', 200, 10, NULL);

There are a few things to notice here.

The column names are only specified once, after the table name, which is the same as if you were inserting a single record.

Several rows are mentioned after the VALUES keyword, and they are separated by a comma.

Running this statement will cause all three rows to be inserted into the table at once.

What's the maximum number of rows you can insert in one statement in MySQL? There is no defined limit like SQL Server. However, if the size of the statement is bigger than the property of "max_allowed_packet", then you'll get an error.

PostgreSQL Insert Multiple Rows

The way to insert multiple rows is the same as SQL Server and MySQL, where you specify the column names once and separate each row in the VALUES clause with a comma.

For example:

1INSERT INTO student (student_id, first_name, last_name, fees_required, fees_paid, enrolment_date)
2VALUES
3(11, 'Jarrad', 'Winston', 700, 300, NULL),
4(12, 'Michael', 'Brown', 750, 500, NULL),
5(13, 'Paul', 'Masters', 200, 10, NULL);

There are a few things to notice here.

The column names are only specified once, after the table name, which is the same as if you were inserting a single record.

Several rows are mentioned after the VALUES keyword, and they are separated by a comma.

Running this statement will cause all three rows to be inserted into the table at once.

Inserting Data From a Query Result

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:

1. Run the SELECT query
2. Copy the output to a spreadsheet file
3. Use formulas to write a series of INSERT statements
4. Copy those INSERT statements to an SQL file
5. Run the SQL file

Sounds like a lot of work, right?

There is an easier way.

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

The syntax for this is:

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

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.

Let's see an example.

1INSERT INTO student
2(student_id, first_name, last_name, fees_required, fees_paid)
3SELECT student_id, first_name, last_name, 1000, 0
4FROM new_students
5WHERE requested_date > '01-JAN-2019';

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.

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 SQL, we can do it with a subquery.

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

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

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:

 1INSERT INTO student
 2(student_id, first_name, last_name, fees_required, fees_paid, enrolment_date, gender)
 3SELECT
 4student_id, first_name, last_name, fees_required, fees_paid, enrolment_date, gender
 5FROM new_student
 6WHERE NOT EXISTS (
 7  SELECT *
 8  FROM student
 9  WHERE student.first_name = new_student.first_name
10  AND student.last_name = new_student.last_name
11);

12 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.

1SELECT * FROM student;

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 a table. Most of the time it's because I don't know the format that is needed, or I get confused between different database vendors.

Inserting a date is pretty simple in SQL and it depends on the vendor.

Oracle Insert Date Value

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:

1CREATE TABLE datetest (
2id NUMBER,
3date_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.

1SELECT value
2FROM SYS.NLS_DATABASE_PARAMETERS
3WHERE parameter = 'NLS_DATE_FORMAT';

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.

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

11 row inserted.

This row was inserted successfully.

How about using the format DD-MM-YY?

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

1SQL 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.

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

11 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:

1INSERT INTO datetest (id, date_test)
2VALUES (4, TO_DATE('JAN 24 2019', 'MON DD YYYY'));
3INSERT INTO datetest (id, date_test)
4VALUES (5, TO_DATE('01-25-2019', 'MM-DD-YYYY'));
5INSERT INTO datetest (id, date_test)
6VALUES (6, TO_DATE('26/01/19', 'DD/MM/YY'));
7INSERT INTO datetest (id, date_test)
8VALUES (7, TO_DATE('01/27/2019', 'MM/DD/YYYY')); 

All of these rows will be inserted.

Let's take a look at the table now.

1SELECT * FROM datetest;

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.

SQL Server Insert Date Value

The easiest way to insert a date value in SQL Server is to enclose the date in string quotes and use a format of either:

• YYYYMMDD for a date
• YYYYMMDD HH:MM:SS for a datetime.

Let's take a look using this sample table:

1CREATE TABLE datetest (
2id INT,
3date_test DATE); 

You can use this INSERT statement, which specifies the date in YYYYMMDD format, to insert the value:

1INSERT INTO datetest (id, date_test)
2VALUES (1, '20190201');

If you're not able to get the value into this format, then you can use the CONVERT function with a specific style.

1INSERT INTO datetest (id, date_test)
2VALUES (1, CONVERT('01 Feb 2019', 106));

The CONVERT function takes a numeric parameter that represents the format of the input date.

MySQL Insert Date Value

To insert a date or datetime in MySQL, you can simply enclose it in a string and specify it in a certain format:

• YYYY-MM-DD (or YY-MM-DD) for date
• YYYY-MM-DD HH:MM:SS (or YY-MM-DD HH:MM:SS) for datetime

Let's take a look using this sample table:

1CREATE TABLE datetest (
2id INTEGER,
3date_test DATE); 

You can use this INSERT statement, which specifies the date in YYYY-MM-DD format, to insert the value:

1INSERT INTO datetest (id, date_test)
2VALUES (1, '2019-03-01');

If you're not able to get the value into this format, then you can use the STR_TO_DATE function with a specific style.

1INSERT INTO datetest (id, date_test)
2VALUES (1, STR_TO_DATE('01 Mar 2019', '%d %b %Y'));

The second parameter of this STR_TO_DATE function can take a series of format values. In this example, the %d relates to the day, the %b relates to an abbreviated month name, and %Y is the four-digit year.

PostgreSQL Insert Date Value

To insert a date in PostgreSQL, you simply enclose it in string quotes in the format of:

• YYYY-MM-DD for date
• YYYY-MM-DD HH:MI:SS for datetime

Let's take a look using this sample table:

1CREATE TABLE datetest (
2id INTEGER,
3date_test DATE); 

You can use this INSERT statement, which specifies the date in YYYY-MM-DD format, to insert the value:

1INSERT INTO datetest (id, date_test)
2VALUES (1, '2019-04-01');

If you're not able to get the value into this format, then you can use the TO_DATE function with a specific style.

1INSERT INTO datetest (id, date_test)
2VALUES (1, TO_DATE('01 Apr 2019', 'DD Mon YYYY'));

The second parameter of the TO_DATE function accepts a string that represents the date that's required.

Insert Into Multiple Tables

Can you insert into multiple tables in SQL?

With some vendors you can, but with others you can't:

• Oracle: Yes, with INSERT ALL and INSERT FIRST
• SQL Server: No
• MySQL: No
• PostgreSQL: No

The steps to use INSERT ALL and INSERT FIRST in Oracle are detailed below.

For other vendors, you have some options:

• You can write two separate INSERT statements as part of a single transaction.
• You can create a stored procedure that inserts data into multiple tables.

Oracle: 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?

 1INSERT [FIRST|ALL]
 2WHEN (condition) THEN
 3INTO table_name (columns)
 4VALUES (values)
 5WHEN (condition) THEN
 6INTO table_name (columns)
 7VALUES (values)
 8ELSE
 9INTO table_name (columns)
10VALUES (values)
11SELECT (columns)
12FROM 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, whereas the INSERT ALL will keep evaluating conditions.

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

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

Oracle: 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.

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.

 1CREATE TABLE jan_enrolments (
 2student_id NUMBER,
 3enrolment_date DATE);
 4
 5CREATE TABLE feb_enrolments (
 6student_id NUMBER,
 7enrolment_date DATE);
 8
 9CREATE TABLE mar_enrolments (
10student_id NUMBER,
11enrolment_date DATE);
12
13CREATE TABLE other_enrolments (
14student_id NUMBER,
15enrolment_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:

 1INSERT ALL
 2WHEN enrolment_date >= '01-MAR-2015' THEN
 3  INTO mar_enrolments (student_id, enrolment_date)
 4  VALUES (student_id, enrolment_date)
 5WHEN enrolment_date >= '01-FEB-2015' THEN
 6  INTO feb_enrolments (student_id, enrolment_date)
 7  VALUES (student_id, enrolment_date)
 8WHEN enrolment_date >= '01-JAN-2015' THEN
 9  INTO jan_enrolments (student_id, enrolment_date)
10  VALUES (student_id, enrolment_date)
11ELSE
12  INTO other_enrolments (student_id, enrolment_date)
13  VALUES (student_id, enrolment_date)
14SELECT student_id, enrolment_date
15FROM 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.

1SELECT student_id, enrolment_date
2FROM jan_enrolments;

1SELECT student_id, enrolment_date
2FROM feb_enrolments;

1SELECT student_id, enrolment_date
2FROM mar_enrolments;

1SELECT student_id, enrolment_date
2FROM other_enrolments;

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

Oracle: 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.

 1INSERT FIRST
 2WHEN enrolment_date >= '01-MAR-2015' THEN
 3  INTO mar_enrolments (student_id, enrolment_date)
 4  VALUES (student_id, enrolment_date)
 5WHEN enrolment_date >= '01-FEB-2015' THEN
 6  INTO feb_enrolments (student_id, enrolment_date)
 7  VALUES (student_id, enrolment_date)
 8WHEN enrolment_date >= '01-JAN-2015' THEN
 9  INTO jan_enrolments (student_id, enrolment_date)
10  VALUES (student_id, enrolment_date)
11ELSE
12  INTO other_enrolments (student_id, enrolment_date)
13  VALUES (student_id, enrolment_date)
14SELECT student_id, enrolment_date
15FROM 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.

1SELECT student_id, enrolment_date
2FROM jan_enrolments;

1SELECT student_id, enrolment_date
2FROM feb_enrolments;

| STUDENT_ID | ENROLMENT_DATE | | --- | | 1 | 1/Feb/15 | | 6 | 12/Feb/15 |

1SELECT student_id, enrolment_date
2FROM mar_enrolments;

| STUDENT_ID | ENROLMENT_DATE | | --- | | 3 | 6/Mar/15 | | 5 | 9/Mar/15 | | 8 | 4/Mar/15 |

1SELECT student_id, enrolment_date
2FROM other_enrolments;

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

Oracle: 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.

Oracle: 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 in Oracle. This does not exist in MySQL, PostgreSQL, or SQL Server.

The syntax for this is:

1INSERT INTO (
2sql_statement WITH CHECK OPTION)
3VALUES (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.

1SELECT student_id, first_name, last_name, fees_required
2FROM student;

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

1INSERT INTO (
2SELECT student_id, first_name, last_name, fees_required
3FROM student
4)
5VALUES (120, 'A', 'A', 500);

11 row inserted.

Let's see the results.

1SELECT student_id, first_name, last_name, fees_required
2FROM student;

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.

1INSERT INTO (
2SELECT student_id, first_name, last_name, fees_required
3FROM student
4WHERE fees_required > 600
5)
6VALUES (121, 'B', 'B', 500);

11 row inserted.

Let's see the results.

1SELECT student_id, first_name, last_name, fees_required
2FROM student;

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.

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

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

Let's see the results.

1SELECT student_id, first_name, last_name, fees_required
2FROM student;

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.

Summary of SQL INSERT INTO Differences Between Vendors

There are parts of the INSERT statement that are different between vendors:

Conclusion

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
• Insert multiple records in a single statement
• Inserting records into multiple tables (Oracle-only)
• Inserting some records into one or more table
• Preventing duplicate values from being inserted