The Project | About Us | Contribute | Donations | License

HOME

The INSERT statement is used to add new rows of data to a table in a database. This is one of the fundamental operations in SQL, allowing you to populate your tables with data.

Key Concepts

Basic Syntax: The simplest form of the INSERT statement.
Inserting Multiple Rows: How to insert more than one row at a time.
Inserting Data from Another Table: Using a SELECT statement within an INSERT statement.
Handling NULL Values: How to deal with columns that can have NULL values.

Basic Syntax

The basic syntax for the INSERT statement is as follows:

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

Example

Let's say we have a table named employees with the following structure:

CREATE TABLE employees (
    id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    hire_date DATE
);

To insert a new employee into this table, you would use the following INSERT statement:

INSERT INTO employees (id, first_name, last_name, email, hire_date)
VALUES (1, 'John', 'Doe', '[email protected]', '2023-10-01');

Explanation

INSERT INTO employees: Specifies the table where the data will be inserted.
(id, first_name, last_name, email, hire_date): Lists the columns that will receive the new data.
VALUES (1, 'John', 'Doe', '[email protected]', '2023-10-01'): Provides the actual data to be inserted into the specified columns.

Inserting Multiple Rows

You can insert multiple rows in a single INSERT statement by separating each set of values with a comma.

Example

INSERT INTO employees (id, first_name, last_name, email, hire_date)
VALUES 
(2, 'Jane', 'Smith', '[email protected]', '2023-10-02'),
(3, 'Alice', 'Johnson', '[email protected]', '2023-10-03');

Explanation

Each set of values is enclosed in parentheses and separated by commas.
This approach is more efficient than executing multiple single-row INSERT statements.

Inserting Data from Another Table

You can also insert data into a table by selecting data from another table.

Example

Suppose we have another table named new_employees:

CREATE TABLE new_employees (
    id INT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    hire_date DATE
);

To insert data from new_employees into employees, you would use:

INSERT INTO employees (id, first_name, last_name, email, hire_date)
SELECT id, first_name, last_name, email, hire_date
FROM new_employees;

Explanation

SELECT id, first_name, last_name, email, hire_date FROM new_employees: Selects the data from new_employees.
The selected data is then inserted into the employees table.

Handling NULL Values

If a column can accept NULL values, you can insert NULL into that column.

Example

INSERT INTO employees (id, first_name, last_name, email, hire_date)
VALUES (4, 'Bob', 'Brown', NULL, '2023-10-04');

Explanation

The email column is set to NULL for this row.
Ensure that the column is defined to accept NULL values in the table schema.

Practical Exercises

Exercise 1

Insert a new row into the employees table with the following details:

id: 5
first_name: 'Charlie'
last_name: 'Davis'
email: '[email protected]'
hire_date: '2023-10-05'

Solution

INSERT INTO employees (id, first_name, last_name, email, hire_date)
VALUES (5, 'Charlie', 'Davis', '[email protected]', '2023-10-05');

Exercise 2

Insert two new rows into the employees table with the following details:

Row 1: id: 6, first_name: 'Eve', last_name: 'White', email: '[email protected]', hire_date: '2023-10-06'
Row 2: id: 7, first_name: 'Frank', last_name: 'Black', email: '[email protected]', hire_date: '2023-10-07'

Solution

INSERT INTO employees (id, first_name, last_name, email, hire_date)
VALUES 
(6, 'Eve', 'White', '[email protected]', '2023-10-06'),
(7, 'Frank', 'Black', '[email protected]', '2023-10-07');

Exercise 3

Insert data from the new_employees table into the employees table.

Solution

INSERT INTO employees (id, first_name, last_name, email, hire_date)
SELECT id, first_name, last_name, email, hire_date
FROM new_employees;

Common Mistakes and Tips

Column Mismatch: Ensure the number of columns in the INSERT statement matches the number of values provided.
Data Types: Make sure the data types of the values match the data types of the columns.
Primary Key: Ensure that the primary key values are unique to avoid conflicts.

Conclusion

The INSERT statement is a fundamental SQL operation that allows you to add new data to your tables. Understanding how to use it effectively is crucial for managing your database. In the next topic, we will cover the UPDATE statement, which is used to modify existing data in a table.

INSERT Statement

Key Concepts

Basic Syntax

Example

Explanation

Inserting Multiple Rows

Example

Explanation

Inserting Data from Another Table

Example

Explanation

Handling NULL Values

Example

Explanation

Practical Exercises

Exercise 1

Solution

Exercise 2

Solution

Exercise 3

Solution

Common Mistakes and Tips

Conclusion

SQL Course

Module 1: Introduction to SQL

Module 2: Basic SQL Queries

Module 3: Working with Multiple Tables

Module 4: Advanced Data Filtering

Module 5: Data Manipulation

Module 6: Advanced SQL Functions

Module 7: Subqueries and Nested Queries

Module 8: Indexes and Performance Optimization

Module 9: Transactions and Concurrency

Module 10: Advanced Topics

Module 11: SQL in Practice

Module 12: Final Project