The article covers the basic syntax of the MySQL INSERT statement and explains how to use the INSERT command in the MySQL table using dbForge Studio for MySQL. Follow our short guide on how to download and install it if you haven’t used it before.
Table of contents
- Introduction to a MySQL INSERT statement
- MySQL INSERT INTO general syntax
- Inserting different data types in MySQL
- MySQL INSERT examples
- MySQL INSERT: ignore errors when inserting data
- Best practices for data insertion in MySQL
- Bulk insert optimization techniques
- INSERT with AUTO_INCREMENT Columns
- INSERT IGNORE vs REPLACE INTO
- Creating the INSERT INTO Query with dbForge Studio for MySQL
- dbForge Studio for MySQL vs the command-line experience
- Comparison of INSERT variants
- Conclusion
- FAQ
Introduction to a MySQL INSERT Statement
The INSERT statement is used to add data to a table. The INSERT INTO command inserts one or multiple rows into a MySQL table. Depending on the number of rows you want to add, the syntax slightly differs.
MySQL INSERT INTO general syntax
When inserting a single row into the MySQL table, the syntax is as follows:
INSERT INTO table_name(column_1,column_2,column_3)
VALUES (value_1,value_2,value_3); In the INSERT INTO query, you should specify the following information:
table_name: A MySQL table to which you want to add a new row.(column_1,column_2,column_3): A list of columns the new row will contain. The columns are separated by a comma in round brackets.(value_1,value_2,value_3): A list of values separated by a comma in round brackets that will be used for each corresponding column.
The number of columns and values should match; otherwise, it fails.
When inserting multiple rows into a MySQL table, the syntax is as follows:
INSERT INTO table_name (column_1, column_2, column_3, ...)
VALUES
(value_list_1),
(value_list_2),
...
(value_list_n); Here you should enter the following information:
table_name: A table into which you want to insert data.column_1, column_2, column_3: A list of columns to store data. The columns are separated by a comma in round brackets.-
value_list_1/2/.../n: A list of values to be inserted in one row. The values are separated by a comma in round brackets. The number of values in each list should match the number of columns specified in(column_1, column_2, column_3, ...).
In addition, rows are separated by commas in the VALUES clause.
Inserting different data types in MySQL
When inserting data into MySQL, the values must match the column’s declared data type. If the type doesn’t match, MySQL may throw an error or attempt to convert the value automatically, which can cause unexpected results.
Inserting strings, numbers, and dates
MySQL requires different formatting depending on the data type. Strings must be quoted, numbers are written plain, and dates follow the YYYY-MM-DD format. Getting this wrong can result in errors or unexpected conversions. Here’s how it works in practice:
- Strings: Must be enclosed in single quotes.
INSERT INTO Customers (ID, FirstName, LastName)
VALUES (3, 'Alice', 'Johnson'); - Numbers: Entered without quotes.
INSERT INTO Products01 (ID, ProductName, Manufacturer, Price, ProductCount)
VALUES (4, 'Galaxy S24', 'Samsung', 899.99, 10); - Dates: Use the YYYY-MM-DD format.
INSERT INTO Orders (OrderID, OrderDate)
VALUES (1, '2025-09-19'); Common issues: Providing a string where a numeric value is expected can lead to implicit type conversion or errors, depending on the SQL mode. Entering an invalid date format (e.g., 19-09-2025) may result in 0000-00-00 or an error.
Handling NULL and Boolean values in INSERT statements
MySQL also supports inserting special values such as NULL and Boolean literals, which behave differently from standard data types. Understanding their behavior helps avoid unexpected results during data entry.
Examples:
- NULL: Represents missing or unknown values.
INSERT INTO Customers (ID, Age, FirstName, LastName)
VALUES (4, NULL, 'David', 'Lee'); - Boolean values: Stored as TINYINT(1), with TRUE mapping to 1 and FALSE mapping to 0.
INSERT INTO Settings (UserID, EmailOptIn)
VALUES (1, TRUE), (2, FALSE); Key notes:
- Attempting to insert NULL into a NOT NULL column causes an error unless INSERT IGNORE is used.
- Booleans can be written as TRUE/FALSE or 1/0; both are valid.
MySQL INSERT examples
Let’s explore some examples of using a MySQL INSERT statement when performing data-related tasks.
To start with, create three tables: Customers, Products01, and Smartphones by executing the script below. But if you’re unsure how to show a table in MySQL, you can use the SHOW TABLES command to view existing tables in your database.
CREATE TABLE Customers
(
ID int,
Age int,
FirstName varchar(20),
LastName varchar(20)
);
-- Creating the Products table
CREATE TABLE Products01
(
ID int,
ProductName varchar(30) NOT NULL,
Manufacturer varchar(20) NOT NULL,
Price decimal NOT NULL,
ProductCount int DEFAULT 0
);
CREATE TABLE Smartphones
(
ID int,
ProductName varchar(30) NOT NULL,
Manufacturer varchar(20) NOT NULL,
Price decimal NOT NULL,
ProductCount int DEFAULT 0
); Learn how to use the CREATE TABLE syntax in MySQL with examples in this article.
MySQL INSERT command used to insert specified values
This command can be used when it is necessary to add values and columns for the specified rows. For example, add data only for the FirstName and LastName columns of the Customers table.
INSERT INTO Customers(ID, FirstName, LastName)
VALUES ('1', 'User', 'Test'); The command inserts values in the FirstName and LastName columns of the Customers MySQL table. Since the Age column uses Null as a default value, MySQL inserts Null into the Age column if you do not explicitly specify its value in the INSERT statement.
MySQL INSERT command used to insert values to all columns
This command can be used to add values to all columns. For example, add values to the Products01 table. Keep in mind that the order of values should match the order of columns in the table.
INSERT INTO Products01
VALUES ('01', 'iPhoneX', 'Apple', 35000, 3); The statement inserts the specified values into all columns of the Products01 table.
MySQL INSERT command used to insert the default value
This command can be used when the default value is specified for the column. For example, the default value for the ProductCount column of the Products01 table equals 0. To insert the default value, execute either of the following queries:
INSERT INTO Products01 (ProductName, Manufacturer, Price, ProductCount)
VALUES ('Nokia 9', 'HMD Global', '41000', DEFAULT);
INSERT INTO Products01 (ProductName, Manufacturer, Price, ProductCount)
VALUES ('Nokia 9', 'HMD Global', '41000', NULL); The statement inserts the default value for the ProductCount column.
What happens if DEFAULT is omitted?
When a column is excluded from an INSERT statement, MySQL automatically assigns its default value. If no explicit default is defined, MySQL falls back to implicit defaults determined by the column type:
- Numeric columns:
0 - String columns: empty string ”
- Date/Time columns:
'0000-00-00'(unless strict mode is enabled, in which case an error is raised)
This behavior allows partial inserts, but relying on implicit defaults can mask data quality issues. For production systems, it’s best practice to define explicit defaults for every column and use them consistently.
MySQL INSERT used to insert a specific value
To insert values into the columns, we can use the SET clause instead of the VALUES clause.
INSERT INTO Customers SET ID=2, FirstName='User2'; In the output, the statement inserts values into the columns based on the explicitly specified values in the SET clause.
MySQL INSERT used to insert data from another table
Using the SELECT clause in the MySQL INSERT INTO statement allows inserting the rows generated by the SELECT statement into the target MySQL table.
INSERT INTO Products01 SELECT * FROM Smartphones; In this case, the INSERT INTO statement copies the rows you retrieved with the SELECT statement from the Smartphones table and inserts them into the Products01 table.
If you want to retrieve specific rows based on the condition, apply filtering options in the WHERE clause. Keep in mind that the structure of both tables should match; otherwise, it won’t work.
INSERT INTO Products01 SELECT * FROM Smartphones WHERE Price=34000; This command adds to the Products01 table values from the Smartphones table where the price equals 34000.
You can also copy and insert specified columns from another table. If you’re unsure which database you’re working in, use the SHOW DATABASES command to view the MySQL list of databases. However, make sure there is no record with the specified name in the target table; otherwise, it fails, and the error occurs.
MySQL INSERT: ignore errors when inserting data
By default, MySQL treats insertion errors as fatal: if any row fails, the entire INSERT is rolled back. This strict behavior preserves data integrity but can disrupt workflows when only a few rows are problematic. Common causes include duplicates, constraint violations, and invalid data types.
Examples:
- Duplicate keys
INSERT INTO Products01
VALUES ('01', 'iPhoneX', 'Apple', 35000, 3);
-- ERROR: Duplicate entry '01' for key 'PRIMARY' This error occurs because the value ’01’ already exists in the primary key column. MySQL enforces uniqueness, so the duplicate insert is rejected.
- NOT NULL
INSERT INTO Customers (ID, FirstName, LastName)
VALUES (10, NULL, 'Smith');
-- ERROR: Column 'FirstName' cannot be null Here, the FirstName column is defined as NOT NULL. Attempting to insert a NULL value breaks the constraint, and MySQL raises an error.
- Type mismatch
INSERT INTO Products01 (ID, Price)
VALUES (11, 'cheap');
-- ERROR: Incorrect decimal value: 'cheap The Price column expects a numeric value. Supplying a string that cannot be converted to a number causes MySQL to reject the insert.
To bypass such issues, use INSERT IGNORE.
INSERT IGNORE INTO Products01
VALUES ('01', 'iPhoneX', 'Apple', 35000, 3); With IGNORE, valid rows are inserted, while problematic rows are silently skipped. MySQL also provides a warning message so you can review what was ignored.
Pro tip: to avoid issues related to syntax and effectively validate code, use MySQL syntax checker.
Best practices for data insertion in MySQL
Writing an INSERT statement that runs without errors is one thing; making sure it’s reliable, consistent, and maintainable is another. Following a few best practices can save you time and prevent data issues later.
Avoiding common INSERT statement errors
Many INSERT problems come from simple mistakes like mismatched columns or constraint violations. These can be avoided by double-checking the query before execution.
Examples of common errors and fixes:
- Column mismatch
INSERT INTO Customers (ID, FirstName, LastName)
VALUES (5, 'Laura');
-- ERROR: Column count doesn't match value count Fix: Ensure each column has a matching value.
INSERT INTO Customers (ID, FirstName, LastName)
VALUES (5, 'Laura', 'Miller'); - NOT NULL constraint violation
INSERT INTO Products01 (ID, ProductName, Manufacturer, Price)
VALUES (6, NULL, 'Sony', 700);
-- ERROR: Column 'ProductName' cannot be NULL Fix: Provide a valid value or define a default.
- Invalid data type
INSERT INTO Products01 (ID, Price)
VALUES (7, 'cheap');
-- ERROR: Incorrect decimal value Fix: Insert numbers without quotes.
Validating data before INSERT
A good practice is to validate data before inserting it into a table. This ensures only valid rows reach your database and prevents unnecessary errors.
| Validation method | What it does | How it helps | Example |
|---|---|---|---|
| Constraints (NOT NULL, CHECK, UNIQUE) | Enforce rules at the schema level. | Stops invalid or missing values before they are stored. | CHECK (Price > 0) ensures prices are always positive. |
| Triggers | Run logic automatically before or after an insert. | Can correct or reject problematic values. | A trigger sets CreatedAt = NOW() when omitted. |
| Front-end validation | Validates user input before it reaches the database. | Reduces failed inserts and improves user experience. | Ensures an email field contains “@” before submission. |
| dbForge Studio for MySQL preview | Generates and previews INSERT scripts in a GUI before execution. | Highlights errors (like type mismatches or missing values) visually. | The developer sees invalid data highlighted before clicking Execute. |
By layering these approaches (schema rules, triggers, application checks, and dbForge Studio for MySQL’s visual preview), you create a safety net that minimizes bad inserts at every stage.
Bulk insert optimization techniques
When dealing with thousands or millions of rows, standard INSERT statements can become slow. MySQL provides several ways to speed up bulk insert operations and reduce system load.
Use transactions
Wrap multiple inserts in a single transaction instead of committing each one separately.
START TRANSACTION;
INSERT INTO Sales (ID, Amount) VALUES (1, 100);
INSERT INTO Sales (ID, Amount) VALUES (2, 200);
COMMIT; Benefit: Reduces disk I/O by committing once instead of per row.
Batch multiple rows
Insert multiple rows in one statement using the VALUES list.
INSERT INTO Sales (ID, Amount)
VALUES (3, 300), (4, 400), (5, 500); Benefit: Faster than single-row inserts because MySQL parses and executes one statement.
LOAD DATA INFILE
The fastest way to import large datasets is using LOAD DATA INFILE.
LOAD DATA INFILE '/path/to/data.csv'
INTO TABLE Sales
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(ID, Amount); Benefit: Ideal for millions of rows, especially when importing from CSV files.
Temporarily disable indexes
If inserting into a table with many indexes, temporarily disable them, perform the insert, then rebuild.
ALTER TABLE Sales DISABLE KEYS;
-- perform bulk inserts
ALTER TABLE Sales ENABLE KEYS; Benefit: MySQL rebuilds indexes once at the end instead of updating per row.
Performance comparison
Bulk insert methods differ in speed and suitability depending on the dataset size and environment. The following table highlights the key options.
| Method | Best for | Relative speed | Notes |
|---|---|---|---|
| INSERT (single row) | Occasional inserts or small datasets | Low | Easy to write, but slow when handling large volumes because each row is committed separately. |
| INSERT (multi-row batch) | Adding thousands of rows in scripts or apps | Medium | Groups rows into one statement, reducing parsing and commit overhead. |
| INSERT DELAYED | Logging or background writes (older systems) | Medium–High | Inserts are queued and written later; they are deprecated and not recommended for modern use. |
| LOAD DATA INFILE | Importing millions of rows from CSV/TSV files | Very High | Fastest method that requires file system access and a strict file format match. |
Takeaway: Use single-row inserts only for small workloads. For larger batches, multi-row INSERT or transactions reduce overhead. LOAD DATA INFILE is the best choice for bulk imports, while INSERT DELAYED should be avoided in modern systems.
INSERT with AUTO_INCREMENT Columns
Many MySQL tables use AUTO_INCREMENT columns, typically for primary keys like ID. Understanding how MySQL handles these columns ensures smooth data insertion and predictable results.
Omitting AUTO_INCREMENT columns
If you skip the AUTO_INCREMENT column in an INSERT, MySQL automatically assigns the next sequential value.
CREATE TABLE Orders (
OrderID INT AUTO_INCREMENT PRIMARY KEY,
ProductName VARCHAR(50)
);
INSERT INTO Orders (ProductName)
VALUES ('MacBook Air'), ('iPad Pro'); Result:
- First row >
OrderID = 1 - Second row
> OrderID = 2
If you insert your own value for OrderID, MySQL accepts it as long as it doesn’t conflict with existing values.
Retrieving the Last Inserted ID
To capture the ID generated by an AUTO_INCREMENT column, use LAST_INSERT_ID().
INSERT INTO Orders (ProductName) VALUES ('iPhone 16');
SELECT LAST_INSERT_ID();
This returns the OrderID of the most recent insert, which is essential for linking records across related tables.
INSERT IGNORE vs REPLACE INTO
Sometimes, inserts fail because of duplicate keys. MySQL offers two strategies:
- INSERT IGNORE: Skips rows that cause duplicate key or constraint errors. Existing rows remain unchanged.
INSERT IGNORE INTO Orders (OrderID, ProductName)
VALUES (1, 'Apple Watch'); - REPLACE INTO: Deletes the existing row with the same key and inserts the new one.
REPLACE INTO Orders (OrderID, ProductName)
VALUES (1, 'Apple Watch'); Key difference: INSERT IGNORE preserves existing data, while REPLACE INTO overwrites it. Choose it based on whether you want to keep or replace the old row.
Creating the INSERT INTO Query with dbForge Studio for MySQL
All the data manipulations, including the Create, Select, Insert, Update, Delete, or Drop operations, can be easily performed with MySQL management tools like dbForge Studio for MySQL. The tool offers a rich set of advanced capabilities and features to boost your productivity and save your time when developing, managing, and deploying MySQL databases in an easy-to-use interface.
Assume we need to add a new language to the database (we use the popular MySQL sample database, sakila, to illustrate the work of dbForge Studio for MySQL).
In dbForge Studio for MySQL, we can use the Generate Script As feature to insert one or multiple rows in the MySQL table.
Favoring a visual interface over writing complex commands? Check the list of best GUI clients for Windows to strengthen your toolset!
To insert information about customers, do the following:
1. In Database Explorer, right-click the required table and select Generate Script As > INSERT > To New SQL Window.
2. The SQL code editor opens, containing the automatically generated script. Insert data for the specified column (name) into the VALUES clause.
3. On the standard toolbar, click Execute. The statement adds the new language (Portuguese) to the bottom line of the grid.
dbForge Studio for MySQL vs the command-line experience
Both dbForge Studio for MySQL and the MySQL command-line client let you perform inserts, but the experience differs significantly. dbForge Studio for MySQL focuses on speed and usability, while the CLI offers flexibility for scripting and automation. The following table highlights the key differences:
| Factor | dbForge Studio for MySQL | Command-Line (CLI) |
|---|---|---|
| Speed of data entry | Visual interface accelerates routine inserts with point-and-click actions and fewer keystrokes. | Requires manual typing of every command, which slows down repetitive tasks. |
| Error detection | Highlights type mismatches or missing values before execution, reducing failed inserts. | Errors are only reported after execution, forcing you to retype or correct manually. |
| Auto-completion | Offers intelligent column and value suggestions, minimizing typos and improving accuracy. | No built-in auto-completion; relies on the developer’s memory or external references. |
Takeaway: dbForge Studio for MySQL provides a faster and more error-resistant workflow for everyday insert operations. At the same time, the CLI remains useful for scripting and automation when a GUI is not available.
Comparison of INSERT variants
MySQL supports several ways to insert data, each with its own strengths. The following table highlights the key differences.
| Method | Use case | Syntax style | Notes |
|---|---|---|---|
| INSERT INTO … VALUES | Add one or more rows directly | Column-value list | The standard method works for most scenarios. |
| INSERT INTO … SET | Insert using key-value pairs | column=value format | Easier to read in scripts, but less common in production. |
| INSERT … SELECT | Copy rows from another table | SELECT as data source | Source and target must have matching structures. |
| INSERT IGNORE | Insert while skipping invalid rows | Same as VALUES | Prevents errors on duplicates or constraint violations. |
| INSERT with DEFAULT | Use predefined column defaults | Column-value list with the DEFAULT keyword | Ensures consistent values when no explicit data is provided. |
Takeaway: VALUES is the go-to option for everyday inserts, while SELECT and IGNORE shine in migrations and bulk imports. Use SET or DEFAULT when readability or defaults are the priority.
Conclusion
In the article, we have reviewed a MySQL INSERT statement and examples, when the command can be used, and provided a particular example of how to work with the MySQL INSERT queries in dbForge Studio for MySQL.
Download a free 30-day trial version of dbForge Studio for MySQL to evaluate all the useful features the tool provides.
FAQ
Can I insert multiple rows with different column sets?
No. All rows in a single INSERT statement must match the same column list. If you need different column sets, use separate INSERT statements or provide defaults for missing values.
How to insert a default value in MySQL?
Use the DEFAULT keyword in the VALUES list, or omit the column entirely. MySQL will insert the column’s defined default, or the implicit default (0, ”, or ‘0000-00-00’ depending on type).
What is the use of INSERT IGNORE?
INSERT IGNORE attempts to insert rows but skips those that would cause errors, such as duplicate keys or constraint violations. Valid rows are still inserted, and MySQL issues a warning instead of stopping execution.
How to bulk insert multiple rows?
Use a single INSERT with multiple value lists.
INSERT INTO Customers (ID, FirstName, LastName)
VALUES (1, 'Alice', 'Smith'), (2, 'Bob', 'Johnson'); For extensive imports, LOAD DATA INFILE is faster than repeated inserts.
