Lesson 7.1: The INSERT INTO Statement

So far, we have focused on retrieving data from existing tables using the SELECT statement. Now, we will begin exploring Data Manipulation Language (DML), starting with how to add new data to your tables using the INSERT INTO statement.

The Basic Syntax

There are two primary ways to use the INSERT INTO statement.

1. Specifying Columns (Recommended)

This is the safest and most common method. You explicitly list the columns you want to fill, followed by the values for those columns.

INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3);

2. Without Specifying Columns

If you are providing values for all columns in the table in the exact order they were defined, you can omit the column names. However, this is less flexible and can lead to errors if the table structure changes.

INSERT INTO table_name
VALUES (value1, value2, value3, ...);

Important Rules for Inserting Data

• Data Types: The values you provide must match the data type of the corresponding column (e.g., you cannot insert text into a numeric column).
• Strings and Dates: Just like in the WHERE clause, string (text) and date values must be enclosed in single quotes (').
• Numbers: Numeric values do not require quotes.
• NULL Values: If a column allows NULL and you don't provide a value for it, it will be filled with NULL (or a default value if one is defined).

Examples

Example 1: Inserting a New Actor

Let's add a new actor to the actor table in the Sakila database.

INSERT INTO actor (first_name, last_name)
VALUES ('JOHNNY', 'DEPP');

Note: We didn't specify the actor_id because it is usually auto-generated by the database.

Example 2: Inserting into Specific Columns

If we have a table with many columns but only want to fill a few:

INSERT INTO customer (first_name, last_name, email, store_id, address_id)
VALUES ('ALICE', 'JOHNSON', 'alice.j@example.com', 1, 5);

Example 3: Multi-row Insert

Most modern databases allow you to insert multiple rows in a single statement by separating the sets of values with commas.

INSERT INTO actor (first_name, last_name)
VALUES 
    ('TOM', 'HANKS'),
    ('MERYL', 'STREEP'),
    ('LEONARDO', 'DICAPRIO');

Key Takeaways from this Lesson:

• The INSERT INTO statement is used to add new rows to a table.
• Explicitly listing column names is recommended for better code reliability and readability.
• String and date values must be enclosed in single quotes.
• You can insert multiple rows at once to improve performance and reduce code.

In the next lesson, we will learn how to Create Tables from scratch and define their structure.