How to Use the SQL INSERT Statement

What is the INSERT Statement?

The INSERT statement is a standard statement in SQL, which means it works in much the same way in Oracle, SQL Server, MySQL, and other databases that implement the standard SQL.

It allows you to insert a row into a table. The table needs to already exist, but doesn’t need to have data in it already.

Let’s see what an INSERT statement looks like.

 

INSERT Statement Syntax

 

The syntax, or the structure of an INSERT statement is:

INSERT INTO table_name (col1, col2, col_n…)
VALUES (val1, val2, val_n…);

Let’s break this down:

• INSERT INTO: The statement starts with INSERT INTO, which means that data is being inserted.
• table_name: You specify the name of the table here. This is where the data is being inserted into.
• col1, col2, col_n: These are the columns to add the data to. You don’t need to specify all columns, and you can specify columns in any order.
• val1, val2, val_n: These are the values to add to the table. They need to match up to the columns you had just mentioned.

What happens if you don’t specify the columns in an INSERT statement? You can write the statement without specifying any columns, but what will happen is the values will go into the columns in the order that the table was set up, which can’t be guaranteed. This can cause your INSERT statements to fail. So, I think it’s always better to specify the columns.

If you specify some columns but not others, then the columns you don’t specify will have NULL values inserted. If they are not allowed to have NULL values, then you’ll get an error.

 

Example Statement

Let’s go through an example of an INSERT statement. We’ll insert a new record into the contact table.

The contact table has these fields:

contact_id, NUMBER

first_name, VARCHAR

last_name, VARCHAR

address, VARCHAR

phone_number, VARCHAR

category, NUMBER

This table is not specific to a type of database. The statement should work on Oracle, SQL, and MySQL, provided you have a table set up with these data types. Sure, there are some specifics that would need to be set with each database (such as using VARCHAR2 for Oracle), but overall, it should work.

We would start with the INSERT INTO table_name part:

INSERT INTO contact

Then we open the brackets, and specify our columns. For this example, we’ll specify all of our columns. We then close the brackets.

INSERT INTO contact (contact_id, first_name, last_name, address, phone_number, category)

You can see I have specified all columns. I have even specified the contact_id. If this is a primary key, you may want to use an auto increment (e.g. in MySQL) or a sequence (in Oracle) to generate this. But this is just our example statement so I won’t use those.

INSERT INTO contact (contact_id, first_name, last_name, address, phone_number, category)
VALUES (

INSERT INTO contact (contact_id, first_name, last_name, address, phone_number, category)
VALUES (102,

INSERT INTO contact (contact_id, first_name, last_name, address, phone_number, category)
VALUES (102, ‘John’,

INSERT INTO contact (contact_id, first_name, last_name, address, phone_number, category)
VALUES (102, ‘John’, ‘Smith’, ’45 Main Street, Seattle’,

INSERT INTO contact (contact_id, first_name, last_name, address, phone_number, category)
VALUES (102, ‘John’, ‘Smith’, ’45 Main Street, Seattle’, ‘1041251092’, 4

INSERT INTO contact (contact_id, first_name, last_name, address, phone_number, category)
VALUES (102, ‘John’, ‘Smith’, ’45 Main Street, Seattle’, ‘1041251092’, 4);