MySQL INSERT INTO

INSERT Methods

🔹 Basic INSERT Syntax

The INSERT INTO statement requires the table name and values to insert. You can specify which columns to insert data into, or omit the column list to insert into all columns. Always list columns explicitly for clarity and to avoid errors when table structure changes over time.

-- Insert with column names (recommended)
INSERT INTO users (name, email, age) 
VALUES ('John Doe', '[email protected]', 25);

-- Insert into all columns (must match order)
INSERT INTO users 
VALUES (1, 'Jane Smith', '[email protected]', 30);

🔹 Inserting Multiple Rows

You can insert multiple rows in a single INSERT statement by separating each set of values with commas. This is more efficient than running multiple INSERT statements separately. Bulk inserts improve performance significantly when adding large amounts of data to your database tables at once.

-- Insert multiple users at once
INSERT INTO users (name, email, age) 
VALUES 
    ('Alice Brown', '[email protected]', 28),
    ('Bob Wilson', '[email protected]', 35),
    ('Carol Davis', '[email protected]', 22);

-- Multiple products
INSERT INTO products (product_name, price, stock) 
VALUES 
    ('Laptop', 999.99, 50),
    ('Mouse', 29.99, 200),
    ('Keyboard', 79.99, 150);

🔹 Inserting Partial Data

You don't need to provide values for all columns. Columns not specified will use their default values or NULL if allowed. This is useful when some fields are optional or have automatic values like timestamps. Always ensure required columns without defaults receive values to avoid errors.

-- Insert only name and email (age will be NULL or default)
INSERT INTO users (name, email) 
VALUES ('Mike Johnson', '[email protected]');

-- Insert with some columns
INSERT INTO products (product_name, price) 
VALUES ('Tablet', 499.99);

🔹 INSERT with SELECT

You can insert data from another table using a SELECT statement. This is useful for copying data, creating backups, or populating tables from existing data. The SELECT query must return columns that match the target table's structure. This powerful technique enables efficient data migration and transformation operations.

-- Copy users to backup table
INSERT INTO users_backup (name, email, age)
SELECT name, email, age FROM users 
WHERE age > 25;

-- Insert filtered data
INSERT INTO premium_users (name, email)
SELECT name, email FROM users 
WHERE subscription = 'premium';

🔹 INSERT with AUTO_INCREMENT

When a table has an AUTO_INCREMENT column (usually the ID), you don't need to provide a value for it. MySQL automatically generates the next sequential number. This ensures unique identifiers for each record without manual tracking. Simply omit the auto-increment column from your INSERT statement for automatic ID generation.

-- ID will be auto-generated
INSERT INTO users (name, email, age) 
VALUES ('Sarah Lee', '[email protected]', 27);

-- Multiple inserts with auto ID
INSERT INTO products (product_name, price) 
VALUES 
    ('Monitor', 299.99),
    ('Webcam', 89.99);

🔹 Common INSERT Errors

Be aware of common mistakes when inserting data. These include mismatched data types, missing required fields, duplicate primary keys, and incorrect column counts. Always validate your data before insertion and use proper error handling. Understanding these errors helps you write more robust and reliable database insertion code.

-- ❌ Wrong: Missing quotes around string
INSERT INTO users (name) VALUES (John);

-- ✅ Correct: Strings need quotes
INSERT INTO users (name) VALUES ('John');