The SQL INSERT INTO Statement
The INSERT INTO statement is used to add new rows (records) into an existing table. If you are new to SQL, make sure you have completed Chapter 3: SQL SELECT first, as you will use SELECT to verify your inserts. There are two main forms:
Form 1 — Insert values for ALL columns:
INSERT INTO table_name
VALUES (value1, value2, value3, ...);Form 2 — Insert values for SPECIFIC columns:
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);We will use this Employees table:
+----+-----------+------------+--------+-----------+
| ID | Name | Department | Salary | City |
+----+-----------+------------+--------+-----------+
| 1 | Priya K. | Sales | 55000 | Mumbai |
| 2 | John D. | Marketing | 62000 | London |
| 3 | Arun M. | IT | 78000 | Delhi |
+----+-----------+------------+--------+-----------+Insert a Single Row (All Columns)
When inserting values for every column, you must provide values in the exact same order as the columns appear in the table:
INSERT INTO Employees
VALUES (4, 'Emma W.', 'Sales', 51000, 'Sydney');After running this, the table now has 4 rows:
+----+-----------+------------+--------+-----------+ | ID | Name | Department | Salary | City | +----+-----------+------------+--------+-----------+ | 1 | Priya K. | Sales | 55000 | Mumbai | | 2 | John D. | Marketing | 62000 | London | | 3 | Arun M. | IT | 78000 | Delhi | | 4 | Emma W. | Sales | 51000 | Sydney | +----+-----------+------------+--------+-----------+
⚠️ Important
When omitting column names, you must provide values for every column in the correct order. If the table has 5 columns, you must supply exactly 5 values. Getting the order wrong will insert data into the wrong columns or cause an error.
Insert into Specific Columns
You can specify which columns to fill. Columns you skip will get their default value (often NULL). This is the recommended approach because it is safer and more readable:
INSERT INTO Employees (Name, Department, City)
VALUES ('Ravi S.', 'IT', 'Mumbai');Here we skipped ID and Salary. If ID is an auto-increment column, the database assigns the next number automatically. Salary will be NULL (empty) — you will learn how to handle NULL values in Chapter 9.
+----+-----------+------------+--------+-----------+ | ID | Name | Department | Salary | City | +----+-----------+------------+--------+-----------+ | 5 | Ravi S. | IT | NULL | Mumbai | +----+-----------+------------+--------+-----------+
💡 Best Practice
Always list column names explicitly in your INSERT statements: INSERT INTO table (col1, col2) VALUES (...). This protects your query from breaking if columns are added, removed, or reordered in the table later.
Insert Multiple Rows
You can insert several rows in a single statement by separating each set of values with a comma. This is much faster than running multiple INSERT statements:
INSERT INTO Employees (Name, Department, Salary, City)
VALUES
('Sara L.', 'HR', 58000, 'New York'),
('Mike R.', 'IT', 69000, 'London'),
('Neha P.', 'Sales', 53000, 'Delhi');+----+-----------+------------+--------+-----------+ | ID | Name | Department | Salary | City | +----+-----------+------------+--------+-----------+ | 6 | Sara L. | HR | 58000 | New York | | 7 | Mike R. | IT | 69000 | London | | 8 | Neha P. | Sales | 53000 | Delhi | +----+-----------+------------+--------+-----------+
Each row of values is enclosed in parentheses and separated by commas. The column list is written only once at the top.
INSERT INTO Rules to Remember
| Rule | Details |
|---|---|
| Text values need quotes | 'Emma W.', 'Sales' — always use single quotes |
| Numbers do NOT need quotes | 51000, 4 — no quotes around numbers |
| Values must match column order | If you list columns, values must be in the same order |
| Skip auto-increment columns | Let the database assign ID automatically |
| Skipped columns get NULL | Unless the column has a default value defined |
| Column count must match | Number of values must equal number of columns listed |
Verify Your INSERT
After inserting data, use a SELECT query to confirm the new rows were added correctly:
SELECT *
FROM Employees
ORDER BY ID DESC;This shows the most recently added rows at the top, making it easy to verify your insert was successful.
Common INSERT Errors
| Error | Cause | Fix |
|---|---|---|
| Column count mismatch | Provided 4 values for 5 columns | Ensure value count matches column count |
| Data type mismatch | Inserted text into a numeric column | Check column data types before inserting |
| Duplicate primary key | Inserted an ID that already exists | Use auto-increment or check existing IDs |
| NOT NULL violation | Skipped a required column | Provide a value for all NOT NULL columns |
| Missing quotes on text | VALUES (Emma) instead of ('Emma') |
Always wrap text values in single quotes |
For a complete reference of the INSERT syntax, see the official MySQL INSERT documentation. Once you have added data, learn how to modify it with SQL UPDATE in Chapter 10 or remove it with SQL DELETE in Chapter 11.
If you work with data regularly, explore our ready-made Dashboard Templates on NextGenTemplates that use SQL-powered data for business reporting.
📺 Visit our YouTube channel @NeoTechNavigators for step-by-step video tutorials and dashboard demos on this topic.
🧪 Try It Yourself
Write an INSERT statement that adds a new employee with these details: Name = ‘Tom B.’, Department = ‘Marketing’, Salary = 65000, City = ‘Singapore’. Use the explicit column names approach.
INSERT INTO Employees (Name, Department, Salary, City)
VALUES ('Tom B.', 'Marketing', 65000, 'Singapore');To verify, run:
SELECT * FROM Employees
WHERE Name = 'Tom B.';Expected Output:
+----+-----------+------------+--------+-----------+ | ID | Name | Department | Salary | City | +----+-----------+------------+--------+-----------+ | 9 | Tom B. | Marketing | 65000 | Singapore | +----+-----------+------------+--------+-----------+
📝 What You Learned in This Chapter
INSERT INTOadds new rows to an existing table- Use
INSERT INTO table VALUES (...)for all columns (values must be in correct order) - Use
INSERT INTO table (col1, col2) VALUES (...)for specific columns (recommended) - Insert multiple rows by separating value sets with commas
- Text values need single quotes; numbers do not
- Auto-increment columns (like ID) can be skipped
- Always verify your insert with a SELECT query
Frequently Asked Questions
INSERT INTO adds one or more new rows to an existing table. It does not modify existing data — for that, you use the UPDATE statement (covered in Chapter 10). Each INSERT creates a brand new record in the table.NOT NULL without a default value must receive a value or the insert will fail.AUTO_INCREMENT.INSERT INTO NewTable (col1, col2) SELECT col1, col2 FROM OldTable WHERE condition. This is called INSERT INTO SELECT and is very useful for data migration and archiving.📖 Chapter 8 of 30 — SQL Tutorial on Neotech Navigators
