• +91-9142290450

  • Hajipur, Bihar, 844101

☰
❮
❯

MySQL INSERT INTO

← Previous Next →

In MySQL, the INSERT INTO statement is used to add new records into a table. It is one of the most fundamental SQL commands because databases are only useful when you store data in them. Without inserting data, there’s nothing to retrieve, update, or analyze.

The INSERT INTO statement is versatile. You can insert a single row, multiple rows at once, or even insert data based on a query from another table. Understanding how to use INSERT correctly ensures that your data is structured and consistent.

Basic Syntax

The simplest form of the INSERT INTO statement is:

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

  • table_name is the name of the table where the record will be added.

  • (column1, column2, column3) specifies the columns to insert data into.

  • VALUES contains the corresponding values for each column.

Example:

INSERT INTO students (name, age, city)
VALUES ('Ananya', 20, 'Mumbai');

This adds a new student named Ananya, aged 20, living in Mumbai, to the students table.

Inserting Data Without Specifying Columns

If you want to insert values into all columns in the order they were defined in the table:

INSERT INTO students
VALUES (1, 'Meera', 19, 'Delhi');

  • This works only if you provide values for every column in the correct order.

  • Omitting a column that allows NULL or has a default value is also possible.

Inserting Multiple Rows

MySQL allows you to insert multiple rows in a single statement:

INSERT INTO students (name, age, city)
VALUES 
    ('Pooja', 18, 'Pune'),
    ('Riya', 21, 'Mumbai'),
    ('Aditi', 22, 'Delhi');

  • This is more efficient than inserting one row at a time.

  • Each row is separated by a comma within the VALUES clause.

Inserting Data into Specific Columns

You can insert data into only some columns, leaving others as NULL or their default values:

INSERT INTO students (name, city)
VALUES ('Meera', 'Delhi');

  • Here, the age column will be NULL (if allowed) or its default value.

  • This approach is useful when not all information is available at the time of insertion.

Using AUTO_INCREMENT Columns

Many tables have a primary key column with AUTO_INCREMENT to automatically generate unique IDs:

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    city VARCHAR(50)
);

When inserting:

INSERT INTO students (name, age, city)
VALUES ('Ananya', 20, 'Mumbai');

  • You do not need to specify id. MySQL automatically assigns the next available number.

  • To see the ID of the last inserted row:

SELECT LAST_INSERT_ID();

Inserting Data Using a SELECT Statement

MySQL allows you to insert data into a table based on query results from another table:

INSERT INTO old_students (name, age, city)
SELECT name, age, city FROM students
WHERE age > 20;

  • This copies records from the students table where age is greater than 20 into the old_students table.

  • It’s helpful for archiving, backups, or data migrations.

Handling Duplicate Keys

If your table has a unique constraint, inserting a duplicate can cause an error. MySQL provides ways to handle this:

1. IGNORE

INSERT IGNORE INTO students (id, name)
VALUES (1, 'Meera');

  • If a duplicate key exists, MySQL ignores the row instead of returning an error.

2. ON DUPLICATE KEY UPDATE

INSERT INTO students (id, name, age)
VALUES (1, 'Meera', 19)
ON DUPLICATE KEY UPDATE age = 19;

  • If the ID already exists, MySQL updates the age instead of inserting a new row.

Best Practices

  1. Always specify columns to avoid inserting data in the wrong order.

  2. Use prepared statements when inserting user input to prevent SQL injection.

  3. Insert multiple rows in one query for efficiency.

  4. Use AUTO_INCREMENT for primary keys to avoid manual ID management.

  5. Handle duplicates carefully using IGNORE or ON DUPLICATE KEY UPDATE.

  6. Validate data before insertion to maintain data integrity.

Real-Life Example

Suppose we have a students table:

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    city VARCHAR(50)
);

Example 1 – Insert a single row:

INSERT INTO students (name, age, city)
VALUES ('Meera', 19, 'Delhi');

Example 2 – Insert multiple rows:

INSERT INTO students (name, age, city)
VALUES 
    ('Pooja', 18, 'Pune'),
    ('Riya', 21, 'Mumbai'),
    ('Aditi', 22, 'Delhi');

Example 3 – Insert with AUTO_INCREMENT:

INSERT INTO students (name, age)
VALUES ('Ananya', 20);

  • id is automatically generated.

Example 4 – Insert from another table:

INSERT INTO alumni (name, age, city)
SELECT name, age, city FROM students
WHERE age > 21;

Summary of the Tutorial

  • The INSERT INTO statement is used to add new records to a table.

  • You can insert a single row, multiple rows, or data from another table.

  • Columns with AUTO_INCREMENT are automatically assigned unique IDs.

  • You can handle duplicates using IGNORE or ON DUPLICATE KEY UPDATE.

  • Proper use of INSERT ensures your database remains organized, efficient, and consistent.

Practice Questions

  1. Insert a single student record into the students table with name, age, and city.

  2. Insert three students at once using a single INSERT statement.

  3. Insert a student record without specifying the age column.

  4. Insert a student record into a table with an AUTO_INCREMENT ID without specifying the ID.

  5. Insert data into a table old_students using a SELECT query from the students table where age is greater than 20.

  6. Insert a record with a duplicate id using INSERT IGNORE to avoid errors.

  7. Insert a record with a duplicate id using ON DUPLICATE KEY UPDATE to update the age.

  8. Insert multiple students with only their names and cities, leaving age as NULL.

  9. Insert a student record with all columns specified, ensuring the correct order of values.

  10. Insert data into a new table courses with columns id, course_name, and duration for three courses in a single query.

← Previous Next →

Try a Short Quiz.

coding learning websites codepractice

No quizzes available.

MySQL

online coding class codepractice

Introduction to MySQL

MySQL SQL Basics

MySQL Functions & Operators

MySQL Joins & Unions

MySQL Grouping & Filtering

Database & Table Management

MySQL Constraints

Date & Views

References

codepractice logo
Login
Go Back Top