SQL INSERT Statement

Introduction

In this chapter, we will learn how to use the INSERT statement in SQL. The INSERT statement is used to add new rows of data into a table. This chapter will cover the syntax, different ways to insert data, and provide examples to help you understand how to use the INSERT statement effectively.

What is the INSERT Statement?

The INSERT statement adds new rows to a table. You can insert data into all columns or specific columns. There are various ways to insert data, including inserting a single row, multiple rows, and inserting data from another table.

Syntax for INSERT

Basic Syntax

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

• table_name: The name of the table where you want to insert data.
• column1, column2, ...: The columns in the table where you want to insert data.
• value1, value2, ...: The values to insert into the specified columns.

Example

Let's assume we have a table named employees and we want to insert a new row into this table.

INSERT INTO employees (first_name, last_name, email)
VALUES ('Ramesh', 'Kumar', 'ramesh.kumar@example.com');

Inserting Data Without Specifying Column Names

If you want to insert data into all columns of a table, you can omit the column names and provide values for all columns in the same order as they are defined in the table.

Syntax

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

Example

Given the employees table structure:

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

You can insert a row without specifying column names:

INSERT INTO employees
VALUES (1, 'Ramesh', 'Kumar', 'ramesh.kumar@example.com');

Step-by-Step Example

1. Create a Sample Table

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

2. Insert a Single Row

INSERT INTO employees (first_name, last_name, email)
VALUES ('Ramesh', 'Kumar', 'ramesh.kumar@example.com');

3. Insert Multiple Rows

You can insert multiple rows in a single INSERT statement by separating the values with commas.

INSERT INTO employees (first_name, last_name, email)
VALUES
('Sita', 'Patel', 'sita.patel@example.com'),
('Arjun', 'Singh', 'arjun.singh@example.com'),
('Priya', 'Sharma', 'priya.sharma@example.com');

4. Verify the Insertions

SELECT * FROM employees;

Output

id	first_name	last_name	email
1	Ramesh	Kumar	ramesh.kumar@example.com
2	Sita	Patel	sita.patel@example.com
3	Arjun	Singh	arjun.singh@example.com
4	Priya	Sharma	priya.sharma@example.com

5. Insert Data Without Specifying Column Names

INSERT INTO employees
VALUES (5, 'Anil', 'Mehta', 'anil.mehta@example.com');

6. Verify the Insertion

SELECT * FROM employees;

Output

id	first_name	last_name	email
1	Ramesh	Kumar	ramesh.kumar@example.com
2	Sita	Patel	sita.patel@example.com
3	Arjun	Singh	arjun.singh@example.com
4	Priya	Sharma	priya.sharma@example.com
5	Anil	Mehta	anil.mehta@example.com

Inserting Data into Specific Columns

If you want to insert data only into specific columns and leave others to their default values, you can specify only those columns in the INSERT statement.

Example

INSERT INTO employees (first_name, email)
VALUES ('Anil', 'anil.kumar@example.com');

Verify the Insertion

SELECT * FROM employees;

Output

id	first_name	last_name	email
1	Ramesh	Kumar	ramesh.kumar@example.com
2	Sita	Patel	sita.patel@example.com
3	Arjun	Singh	arjun.singh@example.com
4	Priya	Sharma	priya.sharma@example.com
5	Anil	NULL	anil.kumar@example.com

Inserting Data from Another Table

You can insert data into a table from another table using a SELECT statement.

Syntax

INSERT INTO table_name (column1, column2, ...)
SELECT column1, column2, ...
FROM another_table
WHERE condition;

Example

Assume we have another table named new_employees and we want to copy data from it to the employees table.

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

Conclusion

The INSERT statement is a fundamental SQL command for adding new data to your database tables. You can insert single or multiple rows, specify only certain columns, omit column names for all columns, and even insert data from other tables. Understanding how to use the INSERT statement effectively is crucial for database management and manipulation. In the next chapter, we will explore more about how to INSERT multiple rows into table.