SQL INSERT INTO: Single Row, Bulk Insert & Copying Data

Populating tables with data is the first step in Data Manipulation Language (DML). The INSERT INTO statement inserts new rows into a database table. In this class, we learn single-row insertion, multi-row bulk loading, inserting default values, and copying data between tables using INSERT INTO SELECT.

1. Standard Single-Row INSERT Syntax

Always specify the target table name, the explicit list of columns being populated, and their corresponding values in matching order:

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

-- Insert a single student record
INSERT INTO students (first_name, last_name, email, course, gpa)
VALUES ('Rahul', 'Sharma', 'rahul@example.com', 'BCA', 3.75);

2. High-Performance Multi-Row Bulk Insert

Instead of executing 100 individual INSERT statements, pass multiple comma-separated value tuples within a single statement:

-- Bulk insert 4 students in a single network round-trip
INSERT INTO students (first_name, last_name, email, course, gpa) VALUES
('Pooja', 'Patel', 'pooja@example.com', 'B.Tech', 3.92),
('Aman', 'Verma', 'aman@example.com', 'BCA', 3.85),
('Neha', 'Gupta', 'neha@example.com', 'MCA', 3.68),
('Vikram', 'Singh', 'vikram@example.com', 'BCA', 3.42);

3. Copying Data with INSERT INTO SELECT

You can copy data from one table directly into another existing table based on any query filter:

-- Copy all top-tier BCA students into an honors table
INSERT INTO bca_honors (student_id, first_name, last_name, gpa)
SELECT student_id, first_name, last_name, gpa
FROM students
WHERE course = 'BCA' AND gpa >= 3.70;

Handling Duplicate Inserts (UPSERT Basics)

In production systems, if an insert violates a primary key or unique index, you often want to update the existing record instead of crashing. MySQL provides ON DUPLICATE KEY UPDATE:

-- MySQL Upsert: Insert new student or update their GPA if email exists
INSERT INTO students (first_name, last_name, email, gpa)
VALUES ('Rahul', 'Sharma', 'rahul@example.com', 3.80)
ON DUPLICATE KEY UPDATE gpa = VALUES(gpa);

Now that our tables contain records, let us master querying them using the SQL SELECT Query!

Frequently Asked Questions (FAQ)